How to programatically control a 3D-sum?

A

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
 
H

Harlan Grove

Ake said:
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.
Click to expand...

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

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
 
H

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. . . .
Click to expand...

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.
Click to expand...
....

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

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. . . .
Click to expand...

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.
Click to expand...
....

CORRECTED!

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

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
 
Top