Auto fill invoice template in Excel with pull down lists
Q: We have a shipping form that we have to fill in. We have created a template so that we do not have to retype the common items. We have like 25 different items, and we could have the need for typing in one of the items or five items (or other combinations) in the body area in this form. Looking for a way to speed up filling in the body area. Let me try to lay out this form to help you understand.
From: name
address
city st zip
To: name
address
city st zip
qty. specification number description
1 12345678 empty box
1 23456789 filled box
Ok, what I want to do is create something that when I start typing the number the rest of the number and the description fill in automatically.
A: No VBA is required, you may be pleased to know.
Somewhere separately in your workbook, you need to create a lookup table. In the first column of the table, enter the code numbers, with the corresponding description in the right hand column. Next - and this step is crucial - name the range containing the data. Call it something like "CodeTable" (no quotes, no spaces). Now select just the first column of the table and name it "CodeTableCol1".
Now go and select the cell or range where you want people to enter the code number. From the Data menu, select Validation. In the 'Allow' pulldown, choose 'list' and in the 'Source' field, type "=CodeTableCol1". In the cell to the right of this one you need to enter a formula. Let's imagine the cell you just set validation in was A5 and the cell you want to show the description in is B5. Here is the formula:
=VLOOKUP(A5,CodeTable,2,FALSE)
If you ever add more items to the code table, you'll have to rename the range to include the extra elements, but thereafter everything else will still work fine. If you might be adding new lines regularly, then you could consider setting up dynamic named ranges, and come back to me if you need guidance on this.
(NB: The data table you create can be anywhere within your workbook, not necessarily on the same worksheet as the formulae. Normally it would need to be on the same sheet for data validation to work, but by naming the range and entering this range name as the validation source, we have a nice loophole to let us get this information off the sheet.)