Excel - how to create a dependent drop down list t

Question

I'm trying to determine how to create a dynamic drop-down list that will take into consideration two different attributes.

In my example below, I have three cost elements: Labor, Infrastructure, and Licenses. Each of these have different levels. For example, Labor at L1 has: Storage Management, Database Managed, and Compute Management. At L2 it has: Manager, Vice President, and Managing Director.

I want in cells E18:F20 for each cell to have a drop down list, for that cost element and for that level.

I've tried using the "Indirect" formula but you need to have the specific named range. I need something that can look up a range based on two attributes, the cost element and level.

Anyone know what I should type in the Data Validation field?

Answer 1

Named ranges cannot have spaces, so your range names will have to use an underscore or omit that character altogether for the dependent ranges. For example "LaborL2".

Define all these named ranges, LaborL1, LaborL2, InfrastructureL1, etc.

Then you can use the Indirect function to concatenate $F18 and G$17. like

=INDIRECT($F18&G$17)

Then you can see the correct list in the dropdown.