Search other Office resources (AccountingWeb, Beancounters Guide, IT Counts):

Excel - Using lists and tables for data entry

Introduction

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.

Approach

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:

• We should be able to select the supplier name from a list derived from the contents of the first column of our suppliers table

• The list should automatically expand to include any new suppliers that are added

• The VLOOKUP() formula should be automatically copied to the next row down when we add a row to our data

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.

Supplier 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:

=VLOOKUP(C2,Suppliers!$A$2:$B$5,2,FALSE)

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.