Use of Indirect in Defined Name
 

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

--
Frank

Use of Indirect in Defined Name
 

Hi

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

or

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

depending on what you are trying to do.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Frank" wrote:

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

--
Frank

Use of Indirect in Defined Name
 

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?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Frank" wrote:

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

--
Frank

Use of Indirect in Defined Name
 

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.

--
Frank


"Shane Devenshire" wrote:

Hi

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

or

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

depending on what you are trying to do.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Frank" wrote:

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

--
Frank

Use of Indirect in Defined Name
 

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)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Frank" wrote:

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.

--
Frank


"Shane Devenshire" wrote:

Hi

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

or

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

depending on what you are trying to do.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Frank" wrote:

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

--
Frank

Use of Indirect in Defined Name
 

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.


--
Frank


"Luke M" wrote:

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)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Frank" wrote:

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.

--
Frank


"Shane Devenshire" wrote:

Hi

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

or

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

depending on what you are trying to do.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Frank" wrote:

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

--
Frank