In my previous article, "Use this sample spreadsheet to make the most of Data Validation drop-down lists," I demonstrated how to build a drop-down list box for a simple invoice. Today’s article will expand upon this process by building a more complex drop-down list for our invoice’s Products section. I’ll also illustrate how absolute cell references can be used to ensure accurate data handling. Before you proceed, I suggest that you read my previous article and download our sample invoice spreadsheet to see these examples in action.
Before we begin
To use readily available data, I imported the Customers And Products list from the Northwind sample database that Microsoft ships with MS Access. If you do not have MS Access, you can obtain this file from Microsoft's Download Center.
Creating the Product Name drop-down list
The Product Name drop-down lists will be created with the same technique used to create the Customer list but will be repeated in cells B14 through B22. This allows for multiple product entries on the same invoice. You can, of course, add more cells to this range if you need more product slots.
Define data range
The drop-down list for the sample invoice’s Product Name area will contain each product’s name. To start, select the cell CA2 and highlight all cells in the column until you reach the list’s end, cell CA68. A quick way to do this is to highlight cell CA2, hold down the [Shift] key, press [End], and then press the Down Arrow key.
We must now assign a name to this selection. There are two methods you can use. The first is to click Insert | Name | Define and then enter the name you wish to use in the input box under Names In Workbook. Or with your selection highlighted, you can go directly to the name box, shown in Figure A, and enter the desired name. For our sample invoice, we will call this list Products. If you click on the Down Arrow to the right of the name box, it will display a list of all the names currently defined in the workbook.
Set up the drop-down list
We can now set up the first Product Name drop-down list. First, select cell B14. This is where the invoice’s Product Name Data Validation drop-down list will be located. Click Data | Validation, and the dialog box shown in Figure B will be displayed. Under Allow, select List and type =Products in the Source text box. Click OK, and your drop-down list is ready to go.
We now need to copy our new drop-down list into cells B15 through B22. To accomplish this, click the very bottom right corner of cell B14 and drag the cursor to cell B22. This will copy not only B14’s cell value but also its Data Validation information. You now have nine separate Product Name drop-down lists.
Autofill product ID, description, price, and subtotal
In cell A14, enter the formula =VLOOKUP(B14,$CA$2:$CJ$68,2) and press [Enter]. Now, when you select an item from the first cell of the Product Name column, the product’s ID will automatically appear in the cell A14. We now need to copy this formula into cells A15 through A22.
A brief note about absolute cell references
Normally when you copy a formula from one cell to another, Excel automatically adjusts the references in the pasted formula to refer to different cells relative to the position of the formula. You must use an absolute reference if you don't want Excel to adjust a formula’s references when copied. You can create an absolute reference to any cell by placing a dollar sign ($) before the parts of the reference that do not change.
Position the cursor near the bottom right corner of cell A15 over what looks like a small black block (see Figure C).
The cursor should become a plus (+) sign (see Figure D).
Hold down the left mouse button and drag the formula down as many rows as you wish the formula to extend. I only selected nine rows in this sample.
Select cell C14 and enter the formula =VLOOKUP(B14,$CA$2:$CJ$68,5). This will populate the Description column. Drag and drop the formula into cells C15 through C22 in the same manner as described above. Select cell F14 and enter the formula =VLOOKUP(B14,$CA$2:$CJ$68,5). This will populate the Price column. Drag and drop the formula into cells F15 through F22 in the same manner as described before. Finally, select cell G14 and enter the formula =F14*E14. This will populate the Sub Total column. Drag and drop the formula into cells G15 through G22 in the same manner as described above.
To be continued…
Unfortunately, we now have an error in our spreadsheet. Figure E shows what happens when no product is present in the Product Name column.
|The #N/A error value occurs when cells referenced in a function or formula do not yet contain data.|
To correct this problem, we need to add two Excel error-handling functions that will compensate for the missing information. I will discuss these functions in my next article and also finish up the sample invoice by adding the sales tax and total formulas.