Cascading Data Validation Lists

..with thanks to  Melodie S. for her help with this post.

A dear associate of mine asked for assistance with the following: How can you dynamically link a validation list, so that choices in one column control the list displayed in another?

The example above illustrates a Region – City validation list. The selection of USA in the Region column will display only U.S. Cities; selecting Europe will display only European ones.

The first step is to create Defined Names for the Regions and each of the City groups.

Creating Defined Names:

1. Select the cells that make up the list. Do not include the list header in your selection.
   For example, to create the Region list in the above example, select E3:E5. 
2. Click in the Name Drop-down.
3. Type a name for the list and press Enter.
   Note: Names must begin with a letter and should not include spaces.
4. Repeat steps 1-3 for each list (i.e., USA, Europe, Australia).

=INDIRECT(ref): Returns the reference specified by a text string.

Using the INDIRECT function to reference the value in the Region column (e.g, Australia), the data validation list will display contents of the matching defined name (e.g., Brisbane, Perth, etc.)

Creating the Data Validation Lists:

1. For the first input column (e.g., Region) select the the cells where data will be input (e.g., B3:B15).
2. On the Data tab, in the Data Tools group, click Data Validation. The Data Validation dialog appears.
3. Click the Allow drop-down and select List.
4. In the Source field type ‘=‘ followed by the appropriate Defined Name (i.e., =Region )
5. Click OK.
6. For the second input column (e.g., City) select the the cells where data is to be input (e.g., C3:C15).
7. On the Data tab, in the Data Tools group, click Data Validation. The Data Validation dialog appears.
8. Click the Allow drop-down and select List.
9. Click in the Source field, type ‘=INDIRECT( ref ), where ref is the cell reference of the first Input cell in the prior column; e.g., =INDIRECT(B3) .
10. Click OK.

Cheers!

hɔuᴉnb

Additional reading:

• Excel: LEFT() and RIGHT()
• Excel: PROPER(), UPPER() & LOWER()
• Excel: Using SEARCH(), MID() & CELL() to Display Sheet Name
• More Excel Tips