Search code examples
excelformuladropdownnamed-ranges

Cell value dependant drop downs (simple INDIRECT(CellValue) no good)

I have searched web, but can not find a way to do cell value dependant drop down, when more than one DD depends on same cell value.

Here is simplified version of what I am trying to do: enter image description here

The first dependant drop down in Cell C4 uses =INDIRECT(C2) formula in Data Validation. And works perfect - When C2 = "_A", C4 drop down is named range _A.

What formula I need to use in C5 Data Validation to still give me drop down based on same Cell C2 value, but this time from other Named Ranges?

Hope my question makes sense. Please help!

Solution

  • What you need are different string to be inputted in each INDIRECT statement.

    This can be done with a VLOOKUP

    First you will need a table matching each possible value in C2 to named ranges for each cell that will use data validation.

    I don't know what your setup is, so I assumed C2 can only have one of two values: _A and _B -- just add rows for more values

    enter image description here

    In C4, use the following formula for Data validation:

    =INDIRECT(VLOOKUP(C2,O2:P3,2))

    And in C5:

    =INDIRECT(VLOOKUP(C2,O2:Q3,3))

    You can have the lookup table wherever you want, just change the references accordingly, also put in whatever named ranges you have in the correct spot in the lookup table