Multi-Tiered Dropdowns Revisited

One of the most popular ongoing topics on my LinkedIn group, Excel Enthusiasts, (catchy name, eh?), is the surprisingly easy-to-create Multi-Tiered Dropdown configuration for more sophisticated research into your data.  Wow, that’s a mouthful, but hang on, it’s Really Cool!

The popularity of the topic is not surprising, since looking up information with the use of only one parameter is insufficient in many cases.  Take for instance that you have a large database with 65 different cities and 14 states. If you want to use the City Name in an Interactive DropDown List to pull up reports based on that city, you are faced with at least a Couple of Possible Glitches:

1. You will be presented with a Long List of City Names in your dropdown (65 in this case) 
2. Many city names Occur in More Than One State (e.g. Dallas, TX & Dallas, GA or Glendale, CA & Glendale, AZ)

There are, of course, a great number of other scenarios where a single parameter is simply lacking in lookup power.  So let’s build a Dropdown List of States that generates a Second Dropdown List of Cities for that state only. This way you can use the parameters of Both the State and the City to assemble your information!

Here is How It is Done:

 

Let’s assume you have a Horizontal Database containing your States and Cities in E2:N12, and your interactive cells set up in B2:C3 as per the illustration above. Note: The list of States would be in the first column of the database, E2:E12.

 

1. Select the Table, E2:N12

     a. Go to Formulas / Defined Names and choose Create from Selection

     b. Assure that the check box with Left Column only is checked and click OK

 

2. Select B3 and Create a Dropdown Box by Using Validation

     a. Under “Allow” choose List and select $E$2:$E$12

 

3. Select C3 and Create a Dropdown Box Using Validation

     a. Under “Allow” choose List and insert the formula =INDIRECT($B$3)

     b. Click OK (Click Yes if you get an error alert…)

 

4. Now to Make It Look a Bit More Professional…

     a. Select, F2:N12 (Note: Do Not include Column E)

     b. Go to the Home tab and select Find & Select from the right-hand side of the ribbon

     c. Select Go to Special and choose Blanks and click OK

     d. Right-click the selected area and Delete / Shift Cells Left

 

And that’s all there is to it! This is a very straightforward technique that results in Multi-Tiered DropDown Lists.  Incredibly useful when setting up Sophisticated Interactive Reports.

 

Interactivity in Reports is, as you may know, a theme that I address with regularity.  The reason is that they are what (whether they know it or not) your users Really, Really Want!