Turn Your Spreadsheet Into A Database Manager
D-Sum Your Expenses
The salesman wants to keep his expense account information in his spreadsheet. He has setup a little database with the following column headings: Date, Amount, Type, and Description. Mr. Traveler's employer will reimburse him for the following types of business expenses: Travel (air fare, auto rentals, gas & oil for his own car, etc.), Meals, Lodging, Telephone & Fax expenses, and Office Supplies purchased on the road. The table below shows the expenses he has entered so far:
| Date | Amount | Type | Description |
| 03/04/2009 | 478.53 | Travel | Air fare to Bermuda |
| 03/04/2009 | 7.00 | Meals | Lunch |
| 03/05/2009 | 55.98 | Lodging | Hotel in Bermuda |
| 03/05/2009 | 17.56 | Meals | Dinner |
| 03/05/2009 | 75.00 | Travel | Auto rental |
| 03/06/2009 | 14.00 | Meals | Lunch |
| 03/06/2009 | 21.00 | Telephone | LD Telephone calls |
| 03/07/2009 | 57.56 | Meals | Dinner |
As time goes on Mr. Traveler will add more expenses to the table illustrated above, and he doesn't want to have to change his formulas every time he does. It is our job to help Mr. Traveler set up his spreadsheet so that his expenses will automatically be totaled for entry in his expense report form according to the expense types listed above.
@DSUM()
makes your spreadsheet a database manager!
Almost all business applications use lists in one way or another. Any list can be more easily manipulated if it is in the form of a database. Relatively short lists and prototypes of more formal applications can be easily managed by your spreadsheet.
| A | B | C | D | |
| 1 | Mr. Traveler’s Expenses | |||
| 2 | DB_Expense | |||
| 3 | Date | Amount | Type | Description |
| 4 | 03/04/2009 | 478.53 | Travel | Air fare to Bermuda |
| 5 | 03/04/2009 | 7.00 | Meals | Lunch |
| 6 | 03/05/2009 | 55.98 | Lodging | Hotel in Bermuda |
| 7 | 03/05/2009 | 17.56 | Meals | Dinner |
| 8 | 03/06/2009 | 75.00 | Travel | Auto rental |
| 9 | 03/06/2009 | 14.00 | Meals | Lunch |
| 10 | 03/06/2009 | 21.00 | Telephone | LD telephone calls |
| 11 | 03/07/2009 | 57.56 | Meals | Dinner |
| 12 | 03/07/2009 | 32.00 | Mailing | Stamps |
| 13 | ||||
| 14 |
(end of reserved database space) |
|||
| 15 | ||||
To make your spreadsheet behave as a database manager there are a few basic skills that make the job easier. The first one is to be able to assign names to single cells and ranges.
In older versions of Excel, first highlight the cell or range of cells that you want to name, then select Insert | Name |Define from the main menu. Type in your name for the selection in the text box at the top of the dialog box that pops up. Other named areas will be listed in the center list box, and the range location in the text box at the bottom. The procedure is similar for Excel 2007. Look for the Defined Names section in the middle of the Formulas Tab. Choose Define Name, Create from Selection, or the Name Manager allows you to creat New names and view names already defined.
Copy the data from the table above into a worksheet. Highlight the cells a couple of rows below the data and add a border at the bottom of those cells. This marks an expansion area for future data entry. You may want to add a border under the column headings. That is for appearance only.
Name your table. Highlight cells A3:D14 and name the range DB_Expense. You’re including the expansion room for the table in your named range. It’s easier for your spreadsheet program to keep things straight if you do not include spaces in range names. Underscores work nicely in place of spaces. To check your work, press F5 (GoTo) and select or type in DB_Expense. The range A3:D14 will be highlighted if all went well with your naming process.
Next we will create criterion ranges for the entries in the Type column. This can be done above or below your DB_Expense table. Do not put anything beside your data because you will later destroy it when you add or delete rows in your table.
In the example I will use space below the data and below the cells with the bottom borders. Skip a row or two and copy the column heading for the "Type" field in your new database. As illustrated below. Then copy one of your entry types in the cell below one containing the word "Type." See the example below.
| A | B | C | D | |
| 14 |
(end of reserved database space) |
|||
| 15 | ||||
| 16 | CR_Travel | CR_Meals | CR_Lodging | CR_Telephone |
| 17 | Type | Type | Type | Type |
| 18 | Travel | Meals | Lodging | Telephone |
| 19 | ||||
| 20 | CR_Mailing | CR_ | CR_ | CR_ |
| 21 | Type | Type | Type | Type |
| 22 | Mailing | |||
| 23 | ||||
A word of caution: For this to work, it absolutely essential that spelling be consistent throughout. For example a cell with the entry "Travel" will not match another with the entry " Travel". The latter has a leading space and the former does not. For this reason, it may be safer to copy and paste rather than retype. Differences in case are ignored by spreadsheet programs. This merely improves appearance for the human eye. Alignment (left, right, center) of the cell entries will have no effect on data recognition by the spreadsheet program.
Now type the names CR_Travel and the others in the example above. Notice we have a few extras for future expansion. Highlight range A17:A18 and name it CR_Travel. Highlight B17:B18 and name it CR_Meals. Repeat the procedure for each of the Type-category cell pairs. Don’t bother with the cells that do not yet have type categories entered. You’ll name them if needed for more categories later. These are the criterion ranges required for the database formulas required to pull the totals for each expense type.
Why bother with names for these ranges wouldn’t range addresses work just as well? Range addresses will work so long as things don’t get moved around. Names will follow the named range wherever they are moved, and they are easier for humans to relate to than row/column addresses. Why the CR_ or DB_ preceding the descriptive names? When you build complex applications names tend to accumulate very quickly. CR_ identifies that named range as a criterion range, and DB_ identifies the table as a database. Later, the application could grow to include DB_Income or DB_Assets or whatever.
Use the GoTo key (F5) to check the locations of some of your named ranges. Now click in the left margin of row 2 to select the whole row and right click for the context menu and insert 5 rows above the field names and DB_Expense label. Use the GoTo key again to check addresses of some of the ranges you just checked. Notice the named locations have moved down a few rows.
Beginning with cell A3 and working down column "A" enter the labels "Travel:", "Meals:", "Lodging:", "Telephone:", and "Mailing:". We’re now ready to enter the functions that will pull the expense totals by type. Put your cellpointer in cell B3. Excel users enter the following formula: =dsum($db_expense,"Amount",cr_travel). 1-2-3 and Quattro Pro users enter: @dsum($db_expense,"Amount",cr_travel).
Copy and paste your B3 entry in cells B4, B5, B6, and B7. Check each copy to be sure the first argument is db_expense. The third argument will read as follows: B3: cr_Travel, B4: cr_Meals, B5: cr_Lodging, B6: cr_Telephone, and B7: cr_Mailing. (You will have to correct the names of these criterion ranges in the third arguments of your copies of the dsum function.) When finished, if all went well, the top of your worksheet should look like this:
| A | B | C | D | |
| 1 | Mr. Traveler’s Expenses | |||
| 2 | ||||
| 3 | Travel: | 553.53 | ||
| 4 | Meals: | 96.12 | ||
| 5 | Lodging: | 55.98 | ||
| 6 | Telephone: | 21.00 | ||
| 7 | Mailing: | 32.00 | ||
| 8 | ||||
| 9 | DB_Expense | |||
| 10 | Date | Amount | Type | Description |
These things done, your totals should compute correctly. If not, here’s some things to look for: 1. Verify the syntax and spelling of the formulas. 2. Either add or remove the "$" to indicate an absolute reference. Some spreadsheets require the absolute reference, and some will not work unless the references are relative—that is the "$" has been removed or never placed.
If you prefer to use the database column number for the second argument in the formula, the Amount field is column 2 in Excel, which counts columns from one, e.g. =dsum($db_expense,2,cr_travel). Lotus and Corel ranges are zero based, so the “Amount” field is column 1 in 1-2-3 and Quattro Pro, as in @dsum($db_expense,1,cr_travel).
When it’s time to expand the database to include more rows, simply select one or more of the blank expansion rows down to the border at the bottom of DB_Expense and insert new rows. The new rows will appear above the selected rows. Your named database range will expand accordingly and the formulas will adjust as well. The order in which data is entered makes no difference. You can add and delete rows in the middle of the database range too.
Warning: One caveat in theLotus or Corel products —do not disturb the first or last rows of the named range. Unless recent versions have improved the way named ranges work, any disturbance of the edge cells of a named range obliterates the range and any references to it in all formulas. You can use copy and paste on these border cells, but never insert, delete or move cells in those positions. In this case, I’m referring to rows 3 and 14, and columns A and D, where the data was entered and the range named before extra rows were inserted to make room for totals. You can select column A to add another column by selecting its top margin, but you do not dare to delete it. Then column A becomes column B. The same applies to column D. The new column goes after C and becomes D. Column D becomes column E. This problem does not exist in Excel.
If you want to add more fields to the database (table columns), and the application has grown to use more than a few formulas referencing the database table, it is often easier to expand from within as described above than to add at the end and then redefine the named range. In the latter case, you would have to edit your formulas to point to the new range in the first argument of the DSUM function.
Pat Tyler