Custom Validation - Dependent Drop Down Lists

Question

I've two columns in a sheet that are interdependent and I want to use validation, drop-down lists, on both as follows:

Column A (TYPE) | Column B (Sub-TYPE)
------------------------------------------|
TypeA, TypeB    | If TypeA SubTypeA1,     |
... TypeN       | SubTypeA2 ... SubTypeAN |
------------------------------------------|

Creating the column A drop down is trivial. How do I create the Column B drop down, that in turn depends on what was chosen in Column A?

Answer 1

You can use INDIRECT function in Excel.

First create a named list consisting of values TypeA, TypeB, ... Let's call it "type"

Then create named lists for each group of subtypes. You can hide these lists later if you want.

Now to make them dependent, first select the cell in which you want the user to select the type and create a drop down list for types. To do this, select Data Validation from Data Menu/Ribbon. Under Allow select List, in Source textbox type "=" following the name of the list. i.e "=type" in our case

Finally use the indirect function to create dependent lists. Select Data Validation from Data Menu/Ribbon. Under Allow select List, in Source textbox type "=INDIRECT(E2)". Change the cell value "E2" to your cell destination where the first selection is done. Click YES if any information pop-up appears.

And here is the final result: