data validation with indirect ref to dynamic range

[Doug Glancy]

Am I correct in thinking that I can't do a data validation list, where I use
Indirect to refer to a cell that contains the name of a dynamic range in
another sheet?

Thanks in advance,

Doug

 

[Ron Coderre]

That appears to be the case. I suspect that using INDIRECT causes Excel to
return an Array Constant, which is not valid as a DV source. Whereas,
directly referencing the Dynamic Range Name returns the actual list.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Doug Glancy]

Thanks Ron,

That's what I'm seeing.

On further investigation it doesn't even seem to work outside of DV on the
same sheet. In other words, if I try an Indirect reference to a dynamic
range on the same sheet it doesn't works. Funny, I thought it did.

Doug

 

[Debra Dalgleish]

There are instructions and a sample file here for dependent validation
with a dynamic list:

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

 

[Doug Glancy]

Debra,

I should of course have checked your site. Brilliant!

Doug