Nested Indirects?

J

Jack Schitt

Sheet1!A1 contains the value 10
Sheet2!A1 contains the value 20
Sheet3!A1 contains the value 30

I tried the effect of array entering the following formula

=SUM(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1"))

Which I expected to return a value 60 (ie 10+20+30).
In other words, I had expected it to simulate the effect of
=SUM(Sheet1:Sheet3!A1)
not array entered (the sheets are adjacent in numerical order)

Instead it returns a value 10. Can someone explain why this is? I had an
idea that there may possibly be confusion over which double-quote marks are
opening quotes and which are closing quotes, but then I tried the effect of
array entering:

=SUM(INDIRECT("Sheet" & ROW($1:$3) & "!A1"))

But this also returned a value 10, so there is something else that I am
missing.
 
F

Frank Kabel

Hi
not tested but try:
=SUMPRODUCT(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1"))
not array entered
 
F

Frank Kabel

Hi
try:
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet" & ROW(INDIRECT("1:3")) &
"'!A1"),"<>0"))
 
A

Aladin Akyurek

You're running up against the derefencing problem whose solution requires a
second round of evaluation:

{=SUM(N(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1")))}

=SUMPRODUCT(N(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1")))

with N() the 1st round and with SUM() or SUMPRODUCT() the 2nd.
 
A

Aladin Akyurek

Typo: derefencing --> dereferencing.

Aladin Akyurek said:
You're running up against the derefencing problem whose solution requires a
second round of evaluation:

{=SUM(N(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1")))}

=SUMPRODUCT(N(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1")))

with N() the 1st round and with SUM() or SUMPRODUCT() the 2nd.
Click to expand...
 
J

Jack Schitt

Thanks.
That works.
I am not sure how to adapt it to ignore error values without resorting to an
array formula, but I can adapt Aladin's solution to that end.
 
J

Jack Schitt

Thanks
That works.

Can you explain the "dereferencing problem" in layman terms, (or point to a
link?)
Thanks again.

--
Return email address is not as DEEP as it appears
Aladin Akyurek said:
You're running up against the derefencing problem whose solution requires
a
second round of evaluation:

{=SUM(N(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1")))}

=SUMPRODUCT(N(INDIRECT("Sheet" & ROW(INDIRECT("$1:$3")) & "!A1")))

with N() the 1st round and with SUM() or SUMPRODUCT() the 2nd.
Click to expand...
 
Top