Solved: Dynamic Drop down based on a dynamic named range

[khalid79m]

Example

I have 4 Different headers , Fruit , Cars, Shapes, Money

underneath these headers I have different types of each header, for example fruit will have apples, oranges etc.

These lists are dynamic so the when I add an item the named range for Fruit_List exapands and if I remove one it decreases.

I have then set up a cell with drop down using data validation and using the indirect function I have tried use the value in d16&"_List" to utilise the drop down but i keep getting an error.

I have attached a workbook ... please help :banghead:

[mbarron]

I think it has to do with evaluation timing. I set up a static Named Range of FruitList and used a validation formula of =INDIRECT($D$16&"List"). The dropdown showed me the list.

[Bob Phillips]

INDIRECT does not work with dynamic ranges.

[khalid79m]

Hi xld is there any other way to do it?

[Bob Phillips]

Try this

[khalid79m]

thjanks xld