Database Tutorial - Continued...
Now click on any cell in column B, a drop-down arrow appears in the cell and you can select from the list of customers you have entered in the Setup sheet. The Data - Validation dialog also allows you to enter custom input and error messages.
Now go to the column heading (cell B1) and use menu item Data - Validation click the "Clear All" button and then click "Ok". This ensures that you can change the heading later if you wish.
c) Lookup lists
We can select a product code using data validation in much the same way. However, it would be nice if, when selecting a product code we also brought in some related information - in our example this would be the product name, price and product type.
Go back to the Setup sheet and enter some product information - our example places column headings in B1:E1, and some data in cells below these. Then use Insert - Name - Define to add a dynamic range called "P_Code" that refers to the product code column only. The formula for the dynamic range is "=Setup!$B$2:INDEX(Setup!$B:$B, COUNTA(Setup!$B:$B))".
Add another dynamic range to refer to all the product information, call it "Product" and use the formula "=Setup!$B$2:INDEX(Setup!$E:$E, COUNTA(Setup!$B:$B))".
Go back to the data sheet, enter the column heading "Product" in cell E1, then enter the following lookup formula in cell E2 - "=VLOOKUP($D2,Simple.xls!Product,2,FALSE)". Enter the column headings "Case_Price" in cell F1, and "ProdCat" in cell G1. Then copy the lookup formula from E2 to F2 and G2, you need to change the index number to 3 and 4 respectively to lookup the correct values. Copy the formulas down to the extent of the data.
We can add a "Sale" heading in cell H1, and enter the formula "=C2*F2" in cell H2, which is simply the number of cases sold times the selling price. Copy this formula for all the data rows also.
Now create a new dynamic range that refers to the data list. Call the name "AcData", the formula we will use is "=Data!$A$1:INDEX(OFFSET(Data!$A:$A,0,COUNTA(Data!$1:$1)-1), COUNTA(Data!$A:$A))". This slightly longer formula makes the range dynamic for both rows and columns. i.e. If we later add a column to the right of our data list, the new column will automatically be included in the range "acData". Notice that the "acData" range includes column headings, this will be useful in the next section.
You now have a simple data list, where data entry is made easier through validation. Related information is added to the data list through formulas.
Select a cell in the first row (containing the headings) and use the menu item Data - Filter - Auto Filter to obtain a filtered list. By clicking the arrows on the column headings, you can now filter the list.
d) Reporting through Pivot Tables
The data list is easy to maintain, but does not in itself provide a good report layout. One of the most powerful reporting tools in Excel is the Pivot Table. (If you are not familiar with Pivot Tables, you can take the Pivot Table tutorial on this site.)
To create a Pivot Table based on the data list, use the menu item Data - Pivot Table and Pivot Chart Report. In Step 1 of the Pivot Table Wizard, select "Microsoft Office Excel List or database" and "PivotTable" (the defaults) and click "Next". In Step 2, enter "AcData" (no quotes) as the range for the Pivot table, then click Next.
In Step 3 of the Pivot Table Wizard, click the Layout button and drag fields to the table diagram as shown below.
Click "Ok" and then click "Finish". The finished Pivot Table should look something like this;
Look at our Pivot Table Tutorial if you wish to learn more about using Pivot Tables.
If you think dynamic ranges are cool, but you find it a little difficult entering all those COUNTA formulas, download our Utility add-in. This lets you build dynamic ranges just by selecting some cells.