Dependent Drop Down Lists With Tables

With dependent drop down lists, you can control what appears in a drop down, based on what was entered in the previous cell. In this example, you select a region, then a country in that region, then an area, and finally a city. See how to set up dependent drop down lists, with tables that make this easy to maintain.

Dependent Drop Down Lists

Long ago, while we were all still using Excel 2003, Roger Govier shared his technique for creating dependent drop down lists using Index.

There is one big lookup table, with Regions listed in the first column, and the remaining columns have the lists for each region and country.

The INDEX function created the applicable drop down list, based on your previous selections on the data entry sheet.

INDEX was an alternative to using volatile functions like INDIRECT or OFFSET, when creating Dynamic Ranges.

Using Excel Tables

When Roger created the Excel 2003 version, the List object had just been introduced, and it wasn’t as full-featured as the Excel Tables feature.

Now, if you’re using Excel 2007 or later, you can create dynamic ranges that are based on tables. So, to take advantage of this improvement, Roger has created 2 updated versions of his technique, using tables as the source for the dynamic ranges.

Version 1: One Table With All Lists

The first version looks similar to the Excel 2003 version, and it has these components:

• 1 data entry table
• 1 lookup table, with all the lists
• 3 named ranges.

In the screen shot below the lookup list has been created, and now it’s being changed to an Excel table, named tblVal. The Regions are listed in the first column, and the remaining columns have the lists for each region and country.

Roger has created two sample files for this version:

• one is macro free – if a value is changed, any selections to the right are not affected
• one has macros – if a value is changed, selections to the right are cleared (in a specific number of columns)

You can read the details and get the sample files for this version here.

Version 2: Multiple Tables and INDIRECT

The second version uses several single-column tables, instead of one giant lookup table. The tables can be in adjacent column, or you can leave gaps.

Roger names each single-column table with the same name that is used in the column heading. So, the table in column J is named Europe.

Why? Because Roger discovered that this allows you to refer to the name directly, and return the range of cells belonging to that name. For example, this formula:

=Europe

will return exactly the same range as this formula

=INDIRECT(Europe[Europe])

Roger has created two sample files for this version:

• one is macro free – if a value is changed, any selections to the right are not affected
• one has macros – if a value is changed, selections to the right are cleared (in a specific number of columns)

You can read the details and get the sample files for this version here.

More Sample Files and Tutorials

You can find all of Roger’s tutorials and sample files listed here: Roger Govier’s Files and Templates

________________