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!
Why is it that poor old accountants are the butt of so many bad jokes? They get so much flack for doing such a thankless task. My personal favourite accountant joke....What does an accountant use for contraception? His personality.
Now, now, before I get loads of complaints, I'm an accountant too...and if we can't laugh at ourselves, 'tis a sad life. And why I am bringing out the bad accountant jokes? Because I am about to head into serious accounting territory, so wanted to get you all loosened up first!
Ok, this is not as serious as having to re-do your tax returns, but I have been asked a specific accounting type question, so will answer it with an accounting based solution. However, please note that while I have recreated a P&L and Trial Balance, you will note that they are a "little" basic and only cover sales and cost of sales. This is because it still conveys the answer required, and doesn't take up heaps of space.
So, the question I was asked was....how do you create a rolling P&L, with each months' figures next to each other, without having to manually key in the figures from separate spreadsheets? I'll show you my solution, and there lots of ways of doing this, but this is the one I like for simplicity:
Lets assume you download a Trial Balance each month and save it somewhere. Lets also assume that your Trial Balance is governed by account numbers. So it looks something like this:
(Before all the accountants start yelling that a Trial Balance should net to zero, like I said, this is fictional and incomplete!)
You also want to have a P&L that looks something like this:
Please note the column headings - they jump from D to O because I have hidden the other columns, which contain March to December, until needed.
So, instead of having to type in each month's figures, or set up a fresh lookup formula and copy it all the way down, you can set this all up using an INDIRECT formula embedded in a VLOOKUP. I briefly touched on this in a prior blog, but I will take you through it step by step now.
First thing is in the set up. Obviously you want to set up your P&L to look just right the first time round. So do it, but only do all the formulas etc for the first month. Once you have it perfected, drag all the formulas across the rest of the columns.
Second thing is, it is better if you paste each month's trial balance into a new sheet within the workbook. This is because an INDIRECT formula will bring back an #REF! error if the workbook it is looking up to is closed.
Right, I have pasted my Trial Balances into my workbook for January and February, and my tabs look like so:
To keep this as simple as possible, I have just shown one formula (for Sales for Widget A) and some of the other more basic formulae:
Now, the step by step walk through (its not nearly as scary as it looks):
IFERROR - this formula is saying "if the results of the following formula comeback as an error, then return 0 instead". That is what the 0 at the end of each line is for. This will also make sure your YTD total doesn't show errors when you drag the formula across for future months.
VLOOKUP - this is your basic VLOOKUP formula as discussed in prior blogs (click here for a refresher). The first brings back your Debit totals, and the second brings back your Credit totals, and these are added together for a total for each account number. This example is based on account numbers, but you can use anything as long as each line is unique. So descriptions are fine too, just make sure the spelling is identical in both sheets.
INDIRECT - instead of having to set up the formulas each month once you have your TB file, you simply point your lookup to the month name in row 3, and if your sheet is named the same, it will perform the lookup in that month's sheet. So when you add in your February figures to the sheet named Feb, it will automatically lookup the numbers. Just be sure to check the TB data is always in the same columns.
Once you have this setup, it really is as simple as pasting a TB into a sheet in your workbook to update your YTD P&L - obviously unhide each month as you need it.
One word of warning - it is always a good idea to have some kind of automatic sum check to compare your P&L totals to the sum of the P&L accounts in your TB. I haven't put a solution in here as everyone's TB will look different, but just bear it in mind. Sometimes automating things too much can make you miss some crucial points (or new TB accounts!).
If you would like a friendly reminder of all new blogposts, please sign up here.
Happy Spreadsheeting!
