How to programatically control a 3D-sum?

[Ake]

I have done
SUM(OFFSET(INDIRECT("Sheet"&number&"!E8")..etcetera) to get a sum from a
specific sheet"number". It works as expected.
Now I want to extend this to get the sum of all sheets from sheet"0" to
sheet"number". However I try it seems that INDIRECT does not like a ":" and
gives me #REFERENCE.
Why can't I simply do
SUM(OFFSET(INDIRECT("Sheet0:Sheet"&number&"!E8")..etcetera)
and how should I actually solve this task.

Best regards / Ake

[Harlan Grove]

"Ake" <[email protected]> wrote...
>I have done
>SUM(OFFSET(INDIRECT("Sheet"&number&"!E8")..etcetera) to get a sum from a
>specific sheet"number". It works as expected.
>Now I want to extend this to get the sum of all sheets from sheet"0" to
>sheet"number". However I try it seems that INDIRECT does not like a ":" and
>gives me #REFERENCE.
>Why can't I simply do
>SUM(OFFSET(INDIRECT("Sheet0:Sheet"&number&"!E8")..etcetera)
>and how should I actually solve this task.

You can't do this because of formula syntax. In Excel, ranges are entirely
contained within single worksheets. 3D references aren't ranges. The OFFSET
function *requires* that its first argument be a range, and it returns #REF!
if it isn't.

As for workarounds, you'd need to show your entire formula.

[Ake]

Thanks Harlan,
A full formula for what I do, that works, is i.e
SUM(OFFSET(INDIRECT("Sheet" & A6 & "!E8"),0,0,1,4)) where cell A6 contains
the sheet number where the sum is to be fetched from (and E8 is the first
cell of interrest (=to be summed) on that sheet. [Returns the sum of a number
of cells from the specific sheet number specified in cell A6]
What I would have _liked_ to do next is something like
SUM(OFFSET(INDIRECT("Sheet0:Sheet" & A6 &"!E8"),0,0,1,4)) where cell A6
contains the last sheet number (n) in the sheet sequence "sheet0, sheet1,
sheet2,...sheetn", across which I want to do the summation.
Thus, in general terms - for all sheets from 0-n, sum all cells of
interrest, please ;-)

Best regards /Ake

[Harlan Grove]

Ake wrote...
>A full formula for what I do, that works, is i.e
>SUM(OFFSET(INDIRECT("Sheet" & A6 & "!E8"),0,0,1,4)) where cell A6 contains
>the sheet number where the sum is to be fetched from (and E8 is the first
>cell of interrest (=to be summed) on that sheet. . . .

If this really is representative, then you could eliminate the OFFSET
call.

SUM(INDIRECT("Sheet"&A6&"!E8:H8"))

>What I would have _liked_ to do next is something like
>SUM(OFFSET(INDIRECT("Sheet0:Sheet" & A6 &"!E8"),0,0,1,4)) where cell A6
>contains the last sheet number (n) in the sheet sequence "sheet0, sheet1,
> sheet2,...sheetn", across which I want to do the summation.
....

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&(ROW(INDIRECT("1:"&(A6+1)))-1)
&"'!A1:A3"),"<>"))

[Harlan Grove]

Ake wrote...
>A full formula for what I do, that works, is i.e
>SUM(OFFSET(INDIRECT("Sheet" & A6 & "!E8"),0,0,1,4)) where cell A6 contains
>the sheet number where the sum is to be fetched from (and E8 is the first
>cell of interrest (=to be summed) on that sheet. . . .

If this really is representative, then you could eliminate the OFFSET
call.

SUM(INDIRECT("Sheet"&A6&"!E8:H8"))

>What I would have _liked_ to do next is something like
>SUM(OFFSET(INDIRECT("Sheet0:Sheet" & A6 &"!E8"),0,0,1,4)) where cell A6
>contains the last sheet number (n) in the sheet sequence "sheet0, sheet1,
> sheet2,...sheetn", across which I want to do the summation.
....

CORRECTED!

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&(ROW(INDIRECT("1:"&(A6+1)))-1)
&"'!E8:H8"),"<>"))

[Ake]

Tnx Harlan, It would have taken me forever to find that function in the
manual ;-)

[Ake]

BTW,

The reason for the "..(OFFSET(.." was to create a function that was
"fill-down/right"-able. With "OFFSET" I can do this with a supporting column,
and get a quite compact writing.
A pondered to use a "ROW()" construct to allow for "fill-down", and it
works. But if you want to have it working both for "fill-down" and
"fill-right" it gets a little bit cumbersome. So therefore I used the
"OFFSET" solution. (Is there an even better solution, perhaps?)

Best regards / Ake