How to programatically control a 3D-sum?
 

I have done
SUM(OFFSET(INDIRECT("Sheet"&number&"!E8")..etceter a) 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

How to programatically control a 3D-sum?
 

"Ake" wrote...
I have done
SUM(OFFSET(INDIRECT("Sheet"&number&"!E8")..etcete ra) 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.

How to programatically control a 3D-sum?
 

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

How to programatically control a 3D-sum?
 

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"),"<"))

How to programatically control a 3D-sum?
 

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"),"<"))

How to programatically control a 3D-sum?
 

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

How to programatically control a 3D-sum?
 

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