Let’s say that you have a large database with 60 different cities and 11 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 Two Potential Problems:
• You are going to be faced with a Long List of City Names in your dropdown (60 in this case)
• Many city names Occur in More Than One State (e.g. Dallas, TX & Dallas, GA or Glendale, CA & Glendale, AZ)
So wouldn’t it be Cool if you could build a Dropdown List of States that would generate a second Dropdown List of Cities for that state only? This way you could use the parameters of Both the State and the City to glean your information!
Here is How You Do This:
First of all, 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 for some cleaning up…
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
That’s It! This really isn’t very difficult, and the resulting Multi-Tiered DropDown Lists are incredibly useful when setting up Sophisticated Interactive Reports.
Have no doubt about it, people will think you are a Genius when you show them this stunning technique. Give it a Try!
1 comment:
Hi Bob,
Great tricks, the multi-tiered options described in your blog worked wonders.
Question though: I am trying to replicate this trick for each cell vertically down from the result cell "indirect(refCell"), however, i need to do this for over 150 cells.
Is there a way of creating a multi-tiered drop down cell where the parameter cell is on, say each cell from $A$2:$A$200, and the result cell is on each cell from $B$2:$B$200?
Post a Comment