View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default 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"),"<"))