In his article "Ensure accurate data entry in Excel by using Data Validation to create drop-down lists," Jeff Davis demonstrated how to create a drop-down list box on an Excel spreadsheet. Today, we are going to build on that idea and show you how to use the drop-down feature in a simple invoice. Download our sample spreadsheet to follow along.
Before we begin
To use readily available data, I imported the list Customers And Products 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.
Sample invoice layout
Let’s first look at our sample invoice’s components, shown in Figure A. The title section is composed of cells A1 through A3. The customer cell, cell B6, has a drop-down list of customers. Then there are also several product fields, B14 through B22, each with a drop-down list. The current tax rate is entered into cell AA1. The data for our Customer drop-down list is stored in columns BA through BJ, and the data for our Products drop-down list is stored in columns CA through CJ. The layout of these data sections is critical to the VLOOKUP function we will use later.
|This image is the working invoice portion of the sample_invoice.xls spreadsheet. The drop-down list arrow will appear in the live spreadsheet when the cursor is placed over the active cell. Data fields, cell AA1, and columns BA through BJ and CA through CJ, included in the sample_invoice.xls, are not shown here.|
Data for the drop-down lists
The sample spreadsheet’s data layout is fairly simple. The Customer data starts at cell BA1 with column headings for each data item. These are CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, and Fax. The Products list starts at column CA1, and the columns are ordered in the same fashion. They are ProductName, ProductID, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsOnOrder, ReOrderLevel, and Discontinued. Remembering the order of these sections will be essential when using the VLOOKUP function. It is also important that the Customers and Products lists be sorted in ascending order. The results of the VLOOKUP function will not be correct if the data is not sorted this way.
Creating the customer drop-down list
Define data range
The drop-down list for the sample invoice’s Customer area will contain every customer’s name. To start, select the cell BA2 and highlight all cells in the column until you reach the list’s end, cell BA90. A quick way to do this is to highlight cell BA2, 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 B, and enter the desired name. For our sample invoice we will call this list “Customers.” 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 Customers drop-down list. First, select cell B6. This is where the invoice’s Customer Data Validation drop-down list will be located. Click Data | Validation, and the dialog box shown in Figure C will be displayed. Under Allow select List and type =Customers into the Source text box. Click OK, and your drop-down list is ready to go.
Autofill customer address, phone, and contact
Being able to quickly select the customer’s name will save time, but it’s even more useful if, when we select the customer’s name, their address, phone number, and contact information are also filled in. This is easily accomplished using the VLOOKUP function.
Select cell B7, where the customer’s street address will appear, and enter the formula =VLOOKUP(B6,BA2:BJ90,4). The VLOOKUP function searches for a value in the leftmost column of a table (in this case, the data in cells BA2 through BJ90) and then returns a value in the same row from a column you specify in the table. The first parameter of our VLOOKUP function, B6, defines the value the function searches for; in this case, the customer selected the value in cell B6. The next parameter, BA2:BJ90, tells the VLOOKUP function which cells to search. (VLOOKUP always starts with the first column in this range.) The last parameter, 4, specifies the column from which the data will be extracted. Our data is structured so the customer’s street address is located in the fourth column, BD. The column count always starts with one and begins with the first column listed in the function’s second parameter, BA2:BJ90.
Use this procedure to add the VLOOKUP function to the rest of the customer fields: City, Region, Country, Postal Code, Contact, Title, Phone, and Fax. For each field, change the function’s last parameter to point to the corresponding column of customer data. Figure D shows the VLOOKUP function for the City field. To test our formulas, choose a customer from the drop-down list in B6, and the rest of the customer’s information should automatically populate the invoice.
I hope this article shows you how useful Excel’s Data Validation feature can be. I’ll continue this exercise in my next article, outlining how to create the Product drop-down list, auto-populate the product information fields, and compute the billing amounts.