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.

datavaldependtables08

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.

DV102b

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.

datavaldependtables04

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.

datavaldependtablesindirect02

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

________________

You may also like...

3 Responses

  1. MF says:

    Hi Debra,
    Thanks for sharing another great article.

    May I ask you a question? which is a concept question that I am a bit confused.

    When I name the Range, e.g. A2:A10 as Range (A1 is the header)
    I can input “=Range” directly as the source for Data Validation

    However, if I turn a range, e.g. B1:B10, into Table; and name the Table as “TRange”
    I cannot input “=TRange” directly as the source. Instead, I need to input =”Indirect(“TRange)”.

    Appreciate your advice.
    Cheers,
    MF

    • Colin says:

      Only the Microsoft Excel designers can truly answer that question!
      It seems that structured tables are NOT just Defined Names on steroids, even though they appear in the Name Manager list and the Formula Bar(along with the function names) when you type a letter. However, they do not appear in the Name box to the left of the Formula Bar.

  1. March 14, 2014

    […] Dalgleish just shared another great article <Dependent Drop Down Lists With Tables>, where you can learn another technique in preparing depending drop down list through data […]

Leave a Reply to Colin Cancel reply

Your email address will not be published. Required fields are marked *