data validation with indirect ref to dynamic range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug Glancy - 18 Sep 2007 03:33 GMT

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

Reply to this Message

Ron Coderre - 18 Sep 2007 03:39 GMT

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)

> 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
[quoted text clipped - 3 lines]
>
> Doug

Reply to this Message

Doug Glancy - 18 Sep 2007 03:50 GMT

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

> 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,
[quoted text clipped - 15 lines]
>>
>> Doug

Reply to this Message

Debra Dalgleish - 18 Sep 2007 04:46 GMT

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

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

> 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?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Doug Glancy - 18 Sep 2007 04:59 GMT

Debra,

I should of course have checked your site. Brilliant!

Doug

> There are instructions and a sample file here for dependent validation
> with a dynamic list:
[quoted text clipped - 4 lines]
>> use Indirect to refer to a cell that contains the name of a dynamic range
>> in another sheet?

Reply to this Message