software

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 (quot;ControlSheet-WklyPL-AllStores.xlsquot;),
the range name (quot;ControlAllquot;) 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 (quot;24-0ct-05quot; 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
(quot;ControlAllquot;) no longer works.

To simplify:
I defined a name for quot;ControlSheet-WklyPL-AllStores.xlsquot; as
quot;ControlBookquot;
I defined a name for a cell referencing the Sheet Name (quot;24-Oct-05quot;) as
quot;Datequot;

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(quot;'quot;amp;ControlBookamp;Dateamp;quot;'!quot;amp;quot;Control Allquot;),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(quot;'quot;amp;ControlBookamp;Dateamp;quot;'!quot;amp;quot;A1:F99 quot;),INDIRECT(quot;'quot;amp;Contro
lBookamp;Dateamp;quot;'!quot;amp;quot;G1:L99quot;)),73,4,$G$1)

Whereas in this example quot;A1:F99quot; and quot;G1:L99quot; make up the array
(A1:F99,G1:L99) that is defined above as quot;ControlAllquot; 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,
SteveSteve,

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)

quot;SubDoerquot; gt; wrote in message oups.com...
gt; Hello,
gt;
gt; I have two corresponding workbooks each with 52 worksheets (one for
gt; each week of the year). The first workbook is a control sheet for
gt; inputing data. The second draws information off of the control sheet
gt; using a system of arrays and range names.
gt;
gt; For example I use variations of the following formula:
gt;
gt; =INDEX('[ControlSheet-WklyPL-AllStores.xls]24-Oct-05'!ControlAll,73,4,$G
gt; $1)
gt;
gt; The Workbook name (quot;ControlSheet-WklyPL-AllStores.xlsquot;),
gt; the range name (quot;ControlAllquot;) which is defined within the
gt; ControlSheet-WklyPL-AllStores.xls, the row numbers, and the columns
gt; (73,4)
gt; all remain constant.
gt;
gt; The only thing that changes is the Sheet Name (quot;24-0ct-05quot; in this
gt; example) and the Area_num ($G$1).
gt;
gt; My first problem was to find a way to enable the Sheet Name to change
gt; based off referencing a cell. I was able to resolve the problem with
gt; the INDIRECT function, but the result is that the range name
gt; (quot;ControlAllquot;) no longer works.
gt;
gt; To simplify:
gt; I defined a name for quot;ControlSheet-WklyPL-AllStores.xlsquot; as
gt; quot;ControlBookquot;
gt; I defined a name for a cell referencing the Sheet Name (quot;24-Oct-05quot;) as
gt; quot;Datequot;
gt;
gt; I tried as many versions as I could think of like this (with
gt; paranthesis, without, etc.) but nothing referencing the range name from
gt; the other workbook seems to work:
gt;
gt; =INDEX(INDIRECT(quot;'quot;amp;ControlBookamp;Dateamp;quot;'!quot;amp;quot;Control Allquot;),73,4,$G$1)
gt; result: #REF
gt;
gt; I was able to get a termporary fix using an extended version of the
gt; following formula (I shortened the definition of it for demonstration):
gt;
gt; =INDEX((INDIRECT(quot;'quot;amp;ControlBookamp;Dateamp;quot;'!quot;amp;quot;A1:F99 quot;),INDIRECT(quot;'quot;amp;Contro
gt; lBookamp;Dateamp;quot;'!quot;amp;quot;G1:L99quot;)),73,4,$G$1)
gt;
gt; Whereas in this example quot;A1:F99quot; and quot;G1:L99quot; make up the array
gt; (A1:F99,G1:L99) that is defined above as quot;ControlAllquot; within the
gt; ControlBook workbook.
gt;
gt; I was hoping someone might know how I can reincorporate the ControlAll
gt; range name back into the formula rather than having to split it up into
gt; the smaller pieces. As I add to the ControlBook and extend the array,
gt; the goal is to merely change the definition of the ControlAll range
gt; name, whereas the temporary fix version would mandate that I change
gt; every formula if I increase the size of the array.
gt;
gt; Thanks to anyone who can help,
gt; Steve
gt;