Have you ever sat in your cosy little office, or claustrophobic little cubicle, and daydreamed? I'm certain you have, and no doubt it was about your holiday, your children, your boss being challenged to a duel at dawn....but have you ever dreamt of finding a way to cut down on your mundane, boredom inducing, life wasting Excel tasks? Like data entry?
Well, daydream no more, because here is a quick and easy little tip that might just make your workload that tiny bit lighter, leaving more time for daydreaming of important things. What's that? You would naturally spend that time doing more value added tasks?....ofcourse you would!!
Anyway, the reader's question was, "When inputting invoice data into a spreadsheet, and each invoice contains a number of items, is there a way I can set up my spreadsheet so that I don't have to re-enter the invoice number and date for each line item?"
There is, and this can be used for any type of data entry that requires repetition of certain cells within a group of lines.
So imagine your data spreadsheet looks like this:
If you are going to add different lines to the same invoice number 1012, then simply use the following formulas in columns A and B (I have them in different colours to make it easy to read here):
You can drag these formulas down for as many rows as you like. They will remain blank until you add in detail into column C. To make sense of the formula, you need to read each formula as saying, "If cell C5 (or whatever line you're on) is empty, then make this cell blank, otherwise copy the data from the cell above."
So if you now add detail into cell C6, the date and invoice will immediately be copied from above. Obviously, a major point to remember is to change your invoice numbers and dates for each new invoice. You can simply type over the formulas in the next line down, and start entering your product details.
Easy as pie and a time saver of note if you have to add a huge number of invoices.
To take this one step further, and make this type of invoice entry even quicker, I would create a standing table of product data, which has all your product details per product code. So it may look something like this:
If you saved it into the same workbook, it would save space. I would then create a dropdown list in your invoice entry sheet in column C, to look up the product codes. To do this, you would use Data Validation (click here for my prior blog for guidelines). You could then use VLOOKUPs to bring back all the relevant information for each product code. (read here for a comprehensive guide to VLOOKUPs).
Using all these tools, you could set up your worksheet so that you only enter your invoice number and date once, you select each product code as required, and enter the quantity. Everything else will fall into place automatically. You could almost go on that holiday, not just daydream about it.
One IMPORTANT point....if you update your product data table with price changes etc, first copy and paste value all the invoice lines you have already entered, otherwise all the previous invoice lines will pick up your new changes!
If you would like to get a friendly reminder of each new blog post, please sign up here.
Happy Halloween, and hope your spreadsheets don't break out in gremlins!
Meet Sam. Sam has a dilemma. He has met the woman of his dreams - lets call her Suzie. They have had a delightful couple of years together, and Sam can picture himself spending the rest of his life with her. He wants to get down on one knee, declare his undying love, and beg for her hand in marriage (yes, soppy for an Excel geek, I know).
So what's the dilemma? Well, Suzie is already turning into a bridezilla. She has been anticipating this day, and presents him with a list of six rings, along with their basic characteristics. When Sam asks her, in some trepidation, what they cost, she airily replies, "about €5000". Sam is a bit of an Excel fan himself so does up this little spreadsheet, to help him budget:
Ah Sam, bless him. He hasn't done his research. He doesn't know about the 4Cs of diamond buying (and for ease in this example, we'll only concentrate on one). He doesn't realise that bigger rocks mean more money, and that platinum is quite a pricey commodity.
So how can we help him? We use the three little words that really mean something...IF, AND and OR.
Now, a couple of caveats before we go further....
a) I am not a jeweller and am sadly not in the habit of buying diamond rings all that often, so these prices are completely made up, and are not particularly logical. Its all for the sake of example....
b) There are other ways of working this problem out - vlookups, modelling tools etc. I am going to do the following examples purely to illustrate how to use IF, AND and OR in formulas.
Lets assume that the base price of €5k applies to a 0.5 carat diamond on a gold band. Sam gets told by his friendly jeweller that all platinum rings incur a 15% markup to cover the cost of the metal. Easy way for Sam to update his sheet? An IF statement.
An IF statement basically says "Is this statement true? If yes - do A, if not - do B". So our formula checks to see whether the metal in column C is platinum, and if it is, it takes the base price and adds the mark up of 15%. If it isn't platinum, the formula just picks up the base price as is.
So Sam thinks he is sorted, but alas no. Our friendly jeweller tells him that a 1 carat diamond carries a hefty markup of 60%, but for this price, they will exclude the additional cost of a platinum band. What now? Well, we use 2 IF statements in one...like so:
This formula uses the first IF statement to check whether the diamond is 1 carat. If it is, it adds on 60%. If the diamond is not 1 carat, then instead of going to the base price, it then checks if the metal is platinum, and adds 15%. This is the 2nd IF statement.
Note: - It is ESSENTIAL to get the logic right in these formulas. If you had checked the metal type first, and you had a platinum ring with a 1 carat diamond, your formula would only show a 15% markup.
You can nest an IF statement quite a few times in one formula, but beware. It gets very complicated and if you find you have 10 or 15 IF statements in one formula, you might want to step back and see if there is an easier way.
Anyway, lets get back to Sam. He heads to another jeweller who has a different pricing system. This jeweller tells Sam that any diamond equal or greater to 0.7 carats, on a band of platinum, has a markup of 30%.
Here we have just nested an AND statement into an IF statement. So instead of one statement needing to be true, BOTH statements need to be true - the diamond must be greater or equal to 0.7, AND the band must be platinum - for us to do step A, which is add on 30%. If BOTH statements are not true, then the formula will revert to the base price.
Now, clearly this pricing is not quite logical. So lets say that if EITHER the band is platinum, or the diamond is greater than or equal 0.7 carats, it gets a 30% markup. We can now use a nested OR statement:
This is the same as the nested AND statement, except in an OR statement, only ONE of the criteria needs to be true for the formula to implement step A.
So there you have it, my favourite 3 little words. Well,when it comes to Excel anyway. Obviously these formulas can get much more complicated, as you can nest away to your hearts content and make things as complicated as you would like. I just wanted to try give you a basic idea of the logic behind these simple, but oh so useful, statements.
For some more ideas on how to use these statements, why not sign up to my newsletter? Just click on this link and fill in the details...
http://www.excelangels.ie/faqs_and_weekly_tips_8.html
Happy Spreadsheeting!
