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