Data Validation, Dynamic Named Ranges, and Dependant Lists
 

Hi everyone,

I am having a problem using dynamic named ranges and dependant lists
with data validation.

I have a dynamic named range (RangeA) that's values are used as the
data validation for a cell, done via a drop down list. This seems to
work nicely so far. The problem comes in the dependant list / dropdown
data validation.

I set the data validation rule for the second cell as INDIRECT(cell
value here). The data validation spits the dummy at this point. The
values contained in RangeA are actually the names of other dynamic
named ranges, for example the first value in RangeA might be "Cities",
and I will have a dynamic named range called "Cities", the values of
which I want in the second cell as choices in a dropdown.

I hope thats not too muddy! This seems to work if the ranges are not
dynamic, but so far no amount of formula playing has managed to get me
a workable solution.

If anyone knows a way to solve this problem I would be grateful.
Perhaps this is approachable as a VBA function for the solution, but
before I try that I want to understand what is causing the problem.

Any help is greatly appreciated

Cheers

The Frog