Use of Indirect in Defined Name

[Frank]

I am trying to use Indirect in a defined name. It works fine as long as the
defined name is on that worksheet. But when I try to reference the Defined
Name from another worksheet, the formula appears to resolve to the current
worksheet.

The format I am using is Indirect(Tables!$A5:B9)

If I am on the Tables worksheet not a problem, but when I am on another
worksheet, it resolves to Indirect(A5B9), losing it's reference to the Tables
worksheet.

Got to be a way to get this to work.
Thanks

 

[Shane Devenshire]

Hi

=Indirect("Tables!$A5:B9")

or

=INDIRECT("Tables!"&SA5:B9)

depending on what you are trying to do.

 

[Luke M]

How are you using an array with the INDIRECT formula? INDIRECT converts text
string to cell references, but by calling an array, how does XL know which
cell you really want to reference?

 

[Frank]

Shane,

Right now I have as the Defined Name definition..

=Tables!$M$5:Indirect(Tables!$N$31)

The intent is to have a table M5:N25 and then limit the upper range with the
Indirect Function of a value in N31.


The above works from the Tables worksheet but not from other worksheets.
Your first suggestion didn't seem to work.

 

[Luke M]

Ah, I think I understand now.
If you meant N31 of which sheet you're on:
=Tables!$M$5:INDIRECT("Tables!N"&$N$31)
If you meant N31 of Tables sheet:
=Tables!$M$5:INDIRECT("Tables!N"&Tables!$N$31)

 

[Frank]

Thanks..

=Tables!$M$5:INDIRECT("Tables!"&Tables!$N$31)

As you pointed out, won't work from the Tables worksheet but does from other
worksheets. And that's exactly what I needed.