Excel multiple drop down list from single Defined name

0
votes

I have a data sheet with defined name with list of contents ex Defined name " Cars"

**Cars**
Honda
Nissan
Ford

Honda, Nissan, Ford have defined name of "Cars" as mentioned above. I have other defined names and list in my data as honda, nissan, and ford and have a drop downs prepared for the list that is dependent on the selection of the above. ex source: 

=INDIRECT($B4)

so if you would choose honda for example a list under would yield new used certified

But in another cell in the file i want another drop down list dependent on the first selection of cars to yield different set of questions ex: honda is selected, 2nd drop down would read

orange
green
yellow

Currently I'm not aware to have a 2nd dependent drop down doing this because to my knowledge the defined name has to be the same as in this case 'Honda', and cannot be a repeat name. All of my other dependent questions and drop downs, depend on having 'honda' selected.

Is there a work around or different formula i should be using?

1
excelexcel-formula

1 Answers

1
votes

There are always crazy formulas you can use! Here's a sheet that should show you everything you need to know... SampleSheet

The lookup table is on the top left. Next to every possible make, it defines (in plain text, not in formulas the cells to look at for that field. The top middle and the top right are tables containing the lists of dependent dropdown items.

So if I select "Honda" in the first dropdown, the second dropdown is filled with values from F2:F3 and the third dropdown is filled with values from J2:J4.

The data validation formulas are written in plain text next to each dropdown. They should be what you would write in the data validation forms. The top-level dropdown is still just a simple reference to the top-level items ("Make"). The dependent dropdowns are of the following form:

=INDIRECT(VLOOKUP([TOP-LEVEL-DROPDOWN-CELL],[DEPENDENT-REFERENCE-TABLE],[COLUMN],FALSE))

This will first find the reference like F2:F3 in the table via VLOOKUP, then will pass it to INDIRECT in order to make it a usable reference for data validation. No named ranges required, though if you wanted to use named ranges you could put them in the reference columns (e.g. replace the F2:F3 with "HondaDescriptions" then define that name to be the range F2:F3).