Don't waste time on "what if's"....

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!

Square pegs into round holes and the wonderful dropdown list

Meet Matt. Matt is a 13 month old toddler, and his mom has given him a shape toy. You know the one I mean; you have to fit the squares into the square-shaped holes, triangles into the triangle-shaped holes…. ? Well, Matt is exploring this toy, and while he might sometimes, by pure luck, get the right shape into the right hole, most of the time he will get it wrong. And what happens then? Either pure frustration, followed shortly by a tantrum, or he’ll get bored and look for another toy.

But why do we give children these toys? Not to torment them, but to teach them shapes, spatial awareness, colours…all sorts of useful things to help them through later life.

What has all this to do with Excel? Well, have you ever received a spreadsheet, and when you try type in your data into a specific cell, you get a message like this:

This is Excel’s version of acting like your mom. It is saying, slowly and clearly, “Try again, little one, the data you input just doesn’t fit in this cell”. Now, while it may make you have a small tantrum and want to throw your laptop against the wall, the person who created the spreadsheet did this for a reason. They have stipulated, quite clearly, that only certain types of data may be entered into that cell. How did they do that? Well, you would tell a 13 month old its “Magic”. For Excel users, the answer is “Data Validation”.

This little gem (or mind bender, depending) can be found on the Data tab of your menu. It looks like this:

As you can see, there are a number of options you can select. With each option, you will be asked for more details. So for example, if you select whole numbers, you then need to select whether the number needs to be greater than, equal to, etc. a specific number. So let’s use this list of children as an example. We are entering the names of children attending preschool, so we would not expect a child to be older than 6. We would select all the cells in the 'Age' column and apply the following settings:

By stipulating this, if you had to then type in 7, you would get an error message. So what happens if you got the same list of children, with their ages already filled in? You need to check there is no-one over 6, so instead of manually checking each age, you can select the whole cell range that contains ages, and apply the same data validation as you did above. Nothing will happen. But if you then select “Circle invalid data” as below:

…all your invalid data will be circled, awaiting your correction.

To prevent future tantrums, you can assist users of your spreadsheet by supplying an Input Message which will appear if the cell is selected. To use this option, fill in the details like so:

This will result in a little pop up box giving the user your message. Likewise, by filling in the details on the Error Alert tab, if someone does fill in the wrong number or text, you can help them by detailing what they need to do to get it right:

If you’re feeling sweet, you might actually lead them in the right direction!

One of the most useful of these data validation tools in the “list” option. This is how you create a dropdown list. If you have a number of items in your list, you can create the list elsewhere in your workbook, and then reference the list co-ordinates in the source box, like so:

However, if your dropdown list is short, maybe it is only Boy or Girl, you can type them directly into the source box, separated by a comma. You would then have a drop down list in your column as shown below:

2 more points:

1)      You may have noticed there is a “Custom” option. This applies to formulas and warrants a blog all on its own. However, I will be giving a taster in my newsletter. Why not sign up?

2)      Data validation rules will be deleted if you copy or drag another cell into the cell with the data validation. So be careful! (Now I sound like your mom….hmmm...sorry about that!)

Happy Spreadsheeting!