| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| INDIRECT and Defined Names | Excel Worksheet Functions | |||
| Application-defined or object-defined error Please Help | Excel Discussion (Misc queries) | |||
| Macro error : Application-defined or object-defined error | Excel Discussion (Misc queries) | |||
| "User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
