Range Name from Another Workbook conflicts with INDEX and INDIRECT

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("'"&Contro
lBook&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

Steve,

That is a little complex to follow, but a couple of things occur to me.

First, does the ControlBook name include the [...] characters.

Secondly, is ControlAll a range name or a name constant. If it is a range,
does it point to the other workbook, in other words is ControlBook and Date
and all the preceding stuff redundant? You could define it as a simple
constant like A1:M10 for instance.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SubDoer" <swoolsey@subwaynw.com> wrote in message
news:1139901566.492511.176640@g44g2000cwa.googlegroups.com...
> 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("'"&Contro
> lBook&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
>