Nested Indirects?

[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.

 

[Frank Kabel]

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

 

[Jack Schitt]

#VALUE!

but nice try

 

[Frank Kabel]

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

 

[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.

 

[Aladin Akyurek]

Typo: derefencing --> dereferencing.

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.

 

[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.

 

[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

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.

 

[Aladin Akyurek]

here one link with search tips:

http://tinyurl.com/5xzcy

Thanks
That works.

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