Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

You can use INDIRECT function in Excel.

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

alt text

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

alt text

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

alt text

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.

alt text

And here is the final result:

alt text

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.