Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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&"'!"&"Control All"),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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Range Name from Another Workbook conflicts with INDEX and INDIRECT
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" wrote in message oups.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&"'!"&"Control All"),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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
Indirect vs. Index | Excel Discussion (Misc queries) | |||
Using INDIRECT function to specify source data | Charts and Charting in Excel |