Interdependent Drop Down Lists

**chollinger** · 03-12-2012, 06:36 PM

I am trying to build a planning tool that will walk people through completing a form of predetermined information. Here is my idea:

I have built a drop down menu that contains a list of location attributes (country, state, building name, circuit ID etc)
Depending on which attribute is selected I would like Excel to offer another drop down menu with more specific sub category details.

For example: We have offices in several states around the US and in many states we have offices in several cities w/i the state. The main category of 'State' was selected and a new drop down menu is now presented with a list of all states we have an office in. California is selected and we have 4 offices w/i this state. A new drop down is presented which shows the address of each office w/i the state of California.

I know how to create the drop down lists but I am not sure how to create these dependencies/relationships or how to make Excel "present" the new drop down lists. Is this possible with Excel?

ps. For the sake of this example the information I am using is greatly simplified from what would actually be presented.

**JieJenn** · 03-12-2012, 06:45 PM

Use Named Range. Name L5:L6 as Florida O5:O6 as Texas. In Cell C3 Data Validation List, enter = Indirect(B3) and in Cell C4, enter =Indirect(B4)

What happen is the data validation is looking at the name range list (Florida for example) to see what are the items contain in that list. Named Range Florida contain 789 W Bull St and 101 N Oil Ave, the formula Indirect will display the two items in the drop down list.

**shg** · 03-12-2012, 07:19 PM

See http://www.contextures.com/xldataval02.html

**chollinger** · 03-13-2012, 06:05 PM

Between both of these previous replies I was able to get the drop down dependencies working. Is there a way to force a change in a dependent cell if an independent cell is changed?

For example: I chose Florida from a list which allowed me to select from Florida Addresses in the next cell. I then realized I should have selected Texas instead. I click on Florida and select Texas from the list to change it. If I'm not careful or get distracted and forget, the Florida address is still populated but now it is next to the Texas cell. If I were to select the Florida address now, only Texas addresses would be available in the list but I would prefer that when Florida is changed to Texas that the Florida address is removed automatically.

**icestationzbra** · 03-13-2012, 07:28 PM

@chollinger - i do not think that is possible without macros... but i will defer to the experts in this thread.

**JBeaucaire** · 03-13-2012, 11:33 PM

Actually you can use conditional formatting to HIDE the old value that was previously selected if it no longer is a match to the dependent cell you changed.

Post up your working sheet and I'll show you what I mean.

Or if you want to figure it out, here's an example:

Dependent Drop Down Lists

The file you want to examine is:
--DependentLists3.xls - 3 levels