Indirect used in an array formula

[Werner Rohrmoser]

Hello,

Environment: Windows XP SP1 Vers. 5.1.2600
Application: Excel XP SP2

Problem:
I have 4 sheets named "sheet1", "sheet2", "sheet3", "sheet4".
sheet1!A1=1, sheet1!A1=2, sheet1!A1=3.

sheet4!A1=sheet1, sheet4!B1=sheet2, sheet4!C1=sheet3.

sheet4!A2="=INDIRECT(A1 & "!A1")"
sheet4!B2="=INDIRECT(B1 & "!A1")"
sheet4!C2="=INDIRECT(C1 & "!A1")"
This works and I get the right results.

When I try an array formula on sheet4 like {=INDIRECT(A1:C1 & "!A1")}
I get "#VALUE!".
The formula is placed in three horizontal cells and I have entered it
with Ctrl+Shift+Enter.
When I calculate a part of the formula it seems to me that it works, I
get the arrays, which I have expected, but at the end it doesn't work.

Any help would be appreciated.
Thanks.

Regards
Werner

 

[Héctor Miguel]

hi, Werner !

... 4 sheets... "sheet1", "sheet2", "sheet3", "sheet4"
... sheet1!A1=1, sheet2!A1=2, sheet3!A1=3.
sheet4!A1=sheet1, sheet4!B1=sheet2, sheet4!C1=sheet3.
sheet4!A2="=INDIRECT(A1 & "!A1")"
sheet4!B2="=INDIRECT(B1 & "!A1")"
sheet4!C2="=INDIRECT(C1 & "!A1")"
This works and I get the right results.
When I try an array formula on sheet4 like {=INDIRECT(A1:C1 & "!A1")} I get "#VALUE!".
The formula is placed in three horizontal cells and I have entered it with Ctrl+Shift+Enter.
When I calculate a part of the formula... I get the arrays, which I have expected, but at the end it doesn't work.

FWIW, if you use a range-array, you have to indicate which index from the array goes into each cell
try again with: =index(indirect(a1:c1&"!a1"),{1;2;3})
[placed in three horizontal cells and entered with ctrl+shift+enter]

hth,
hector.

 

[Werner Rohrmoser]

Thanks,
I use it for a file which has a sheet for every week
of the year (for example week 01-05, week 02-05,....., week 52-05).
On a consolidation sheet I pull data from the week sheets.
At the beginning of a new year a vba-procedure renames my week sheets
according to the current year (for example week 01-06, week 02-06,
etc.),
writes new headers (week 01-06, week 02-06, etc.) for a table in which
these headers are used as a part of my formulas.
To avoid errors and changes by users I like to use array formulas.

In short words: automatic setup for a new year.

Werner

 

[Werner Rohrmoser]

Hi Hector,

this afternoon I've had another idea and it works as well.
{=N(INDIRECT(A1:C1 & "!A1"))} or
in my real case {=N(INDIREKT("'" & C1:BE1 & "'!M7"))},
because I have 55 sheets.

Regards
Werner

hi, Werner !

... 4 sheets... "sheet1", "sheet2", "sheet3", "sheet4"
... sheet1!A1=1, sheet2!A1=2, sheet3!A1=3.
sheet4!A1=sheet1, sheet4!B1=sheet2, sheet4!C1=sheet3.
sheet4!A2="=INDIRECT(A1 & "!A1")"
sheet4!B2="=INDIRECT(B1 & "!A1")"
sheet4!C2="=INDIRECT(C1 & "!A1")"
This works and I get the right results.
When I try an array formula on sheet4 like {=INDIRECT(A1:C1 & "!A1")}I get "#VALUE!".
The formula is placed in three horizontal cells and I have entered it with Ctrl+Shift+Enter.
When I calculate a part of the formula... I get the arrays, which I have expected, but at the end it doesn't work.

FWIW, if you use a range-array, you have to indicate which index from thearray goes into each cell
try again with: =index(indirect(a1:c1&"!a1"),{1;2;3})
[placed in three horizontal cells and entered with ctrl+shift+enter]

hth,
hector.