Indirect & Dynamic Range

I assume B1 value is the named range name? That works for me if I have a named range in B1 and Tables is the name of the workbook?

Not sure if that is true for your situation or not?

Also I tried this on 2003, as I don;t ahve 2007 at work.

Yes B1 on Sheet1 is the name of the named range...eg Branch
On the Tables worksheet is where the named range Branch resides...

But when i put in INDIRECT(B1) in the data validation it brings up a dialog box which states:
The Source currently evaluates to an error. Do you want to continue?

If I change the source in the data validation to =Branch (ie going directly based upon the named range rather than using INDIRECT) then it works fine...

but i really need to reference it via INDIRECT since the value in B1 can change to any of 3 named ranges (ie controlled via a validation list)...

***EDIT***
I think I found the cause...I'm using dynamic named ranges...if i switch to regular named range it works fine...any thoughts on how to get around this since my ranges really need to be dynamic...would my best best bet be to let the external query refresh on open then through vba name the range?

TheNoocH said:

***EDIT***
I think I found the cause...I'm using dynamic named ranges...if i switch to regular named range it works fine...

Click to expand...

The diagnosis is right.

any thoughts on how to get around this since my ranges really need to be dynamic...would my best best bet be to let the external query refresh on open then through vba name the range?

Click to expand...

D2 is data validated with List consisting of items

SubListA
SubListB
SubListC

Each item is a dynamic named range.

Data validate F2 with...

=CHOOSE(MATCH(D2,List,0),SubListA,SubListB,SubListC)

while the Ignore Blank option is unchecked.