Validation Filtering

[John]

I am having difficulty combining dynamic ranges together with the
indirect function in data validation. I have a number of dynamic ranges
that are created using the offset formula. I then have a worksheet with
data validation. The data validation limits the choices a user can
select from by converting a previous election into a range. e.g I have
a dynamic range called "Collectables", the user first selects
"Collectables" from a drop down list in cell A4, they then move onto
another dropdown list in cell B4, the data validation is set to
=INDIRECT(A4). This should allow the users to selct an item that is
within the "Collectables" range. However it DOSN'T.

If I make the range non dynamic i.e. Make it a fixed range it works
fine.
If I leave the dynamic range and change the data validation to
=Collectables it works fine.

It just won't work with both Dynamic ranges and the INDIRECT
function.

Is this a bug with Excel or is there a reason for it not working? Is
there a way around it?

 

[Dave Peterson]

I think that this is just the way excel works.

If that dynamic range doesn't change very often (maybe just by the developer),
maybe you could use the workbook_Open event to assign the name.

If it changes by user changes, maybe you could use a worksheet_change event.

 

[Debra Dalgleish]

Also, there's a workaround explained here:

http://www.contextures.com/xlDataVal02.html#Dynamic