Range Name from Another Workbook conflicts with INDEX and INDIRECT

[SubDoer]

Hello,

I have two corresponding workbooks each with 52 worksheets (one for
each week of the year). The first workbook is a control sheet for
inputing data. The second draws information off of the control sheet
using a system of arrays and range names.

For example I use variations of the following formula:

=INDEX('[ControlSheet-WklyPL-AllStores.xls]24-Oct-05'!ControlAll,73,4,$G$1)

The Workbook name ("ControlSheet-WklyPL-AllStores.xls"),
the range name ("ControlAll") which is defined within the
ControlSheet-WklyPL-AllStores.xls, the row numbers, and the columns
(73,4)
all remain constant.

The only thing that changes is the Sheet Name ("24-0ct-05" in this
example) and the Area_num ($G$1).

My first problem was to find a way to enable the Sheet Name to change
based off referencing a cell. I was able to resolve the problem with
the INDIRECT function, but the result is that the range name
("ControlAll") no longer works.

To simplify:
I defined a name for "ControlSheet-WklyPL-AllStores.xls" as
"ControlBook"
I defined a name for a cell referencing the Sheet Name ("24-Oct-05") as
"Date"

I tried as many versions as I could think of like this (with
paranthesis, without, etc.) but nothing referencing the range name from
the other workbook seems to work:

=INDEX(INDIRECT("'"&ControlBook&Date&"'!"&"ControlAll"),73,4,$G$1)
result: #REF

I was able to get a termporary fix using an extended version of the
following formula (I shortened the definition of it for demonstration):

=INDEX((INDIRECT("'"&ControlBook&Date&"'!"&"A1:F99"),INDIRECT("'"&ControlBook&Date&"'!"&"G1:L99")),73,4,$G$1)

Whereas in this example "A1:F99" and "G1:L99" make up the array
(A1:F99,G1:L99) that is defined above as "ControlAll" within the
ControlBook workbook.

I was hoping someone might know how I can reincorporate the ControlAll
range name back into the formula rather than having to split it up into
the smaller pieces. As I add to the ControlBook and extend the array,
the goal is to merely change the definition of the ControlAll range
name, whereas the temporary fix version would mandate that I change
every formula if I increase the size of the array.

Thanks to anyone who can help,
Steve

 

[keepITcool]

Important: indirect to an external workbook works only for OPENED
workbooks. (Else use function INDIRECT.EXT from addin MOREFUNC.XLL
from Laurent Longre at http://xcell05.free.fr/

Assume
Book "test 1.xls"
Sheet "24-05"
Range "Part"
Value "Widget X"

Name defined at SHEET LEVEL thus fullname = '24-05'!Part

External reference looks like '[test 1.xls]24-05'!Part
Formula in e.g. B10 like ="'["&B7&"]"&B8&"'!"&B9
Indirect(B10) = "WidgetX"

Note the use of apostrophes and square brackets!




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


SubDoer wrote :

Hello,

I have two corresponding workbooks each with 52 worksheets (one for
each week of the year). The first workbook is a control sheet for
inputing data. The second draws information off of the control sheet
using a system of arrays and range names.

For example I use variations of the following formula:

=INDEX('[ControlSheet-WklyPL-AllStores.xls]24-Oct-05'!ControlAll,73,4,
$G$1)

The Workbook name ("ControlSheet-WklyPL-AllStores.xls"),
the range name ("ControlAll") which is defined within the
ControlSheet-WklyPL-AllStores.xls, the row numbers, and the columns
(73,4)
all remain constant.

The only thing that changes is the Sheet Name ("24-0ct-05" in this
example) and the Area_num ($G$1).

My first problem was to find a way to enable the Sheet Name to change
based off referencing a cell. I was able to resolve the problem with
the INDIRECT function, but the result is that the range name
("ControlAll") no longer works.

To simplify:
I defined a name for "ControlSheet-WklyPL-AllStores.xls" as
"ControlBook"
I defined a name for a cell referencing the Sheet Name ("24-Oct-05")
as "Date"

I tried as many versions as I could think of like this (with
paranthesis, without, etc.) but nothing referencing the range name
from the other workbook seems to work:

=INDEX(INDIRECT("'"&ControlBook&Date&"'!"&"ControlAll"),73,4,$G$1)
result: #REF

I was able to get a termporary fix using an extended version of the
following formula (I shortened the definition of it for
demonstration):

=INDEX((INDIRECT("'"&ControlBook&Date&"'!"&"A1:F99"),INDIRECT("'"&Cont
rolBook&Date&"'!"&"G1:L99")),73,4,$G$1)

Whereas in this example "A1:F99" and "G1:L99" make up the array
(A1:F99,G1:L99) that is defined above as "ControlAll" within the
ControlBook workbook.

I was hoping someone might know how I can reincorporate the ControlAll
range name back into the formula rather than having to split it up
into the smaller pieces. As I add to the ControlBook and extend the
array, the goal is to merely change the definition of the ControlAll
range name, whereas the temporary fix version would mandate that I
change every formula if I increase the size of the array.

Thanks to anyone who can help,
Steve