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

 

[Bob Phillips]

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)