Lunchtime learning - Excel
to lunchtime menu
Do make sure that any tips and suggestions work as you expect them to in your own particular circumstances.
Search other Office resources (AccountingWeb, Beancounters Guide, IT Counts):
Excel - Using lists and tables for data entry
A recent question on AccountingWeb about automating data entry provoked some interesting responses involving dynamic formulae and the use of VBA code and the worksheet change event. I'm not a great fan of using Excel for data entry beyond very simple lists, preferring a database form where the requirements become more complex, but, if you are going to use Excel for this sort of thing, using an Excel 2003 List or Excel 2007 Table can simplify the process.
The original question involved the need to enter a supplier name in one column and return an expense type in the adjacent cell. For example, if 'Shell' was entered in A2, 'Motor expenses' should automatically appear in B2. At its simplest this would just require a table of suppliers and expense types and the use of VLOOKUP() to match the supplier name and return the expense type. However, to work properly we would need to go beyond this:
As our previous article on Lists and Tables showed, they can be used to ensure Data Validation lists automatically expand to include added rows. So we will create our suppliers table as a List/Table.
We will also create our data entry area as a List/Table.
Here is our supplier table. We've right-clicked in the table and chosen 'Create List' and confirmed the range of our list and that it has headers. We'll allocate the name SupplierList to the range A2:A5 for use in our data validation cell.
Data Entry table
Now we'll construct our data entry table. We'll assume we need to enter a date and a value, then choose the supplier and have the expense type looked up from the supplier table.
So in cell C2 we use Data Validation to use the 'SupplierList' range we set up:
In D2 we enter our VLOOKUP() formula:
Then, as for the suppliers table we right click to 'Create List'
When we enter a new item in our List, our Data Validation setting and VLOOKUP() formula will automatically be copied to our new row. Also, pressing 'Return' will take us through each cell in our data entry row, then onto the next row down.
If we add items to our suppliers table, they will automatically appear in our Data Validation list and work correctly in our VLOOKUP() formula.
|© 1998 The Knowledge Base. Disclaimer Contact firstname.lastname@example.org
Design by Reading Room Ltd 1998