Crazy Excel 2003 Formula Question

[Guest]

I have an Excel file where I'm using a 3D formula that calculates numbers
from 4 different sheets. All of the sheets are formatted the same so the
cell numbers are in the same position just different cells. My Problem is
this when I use Auto fill to copy the formula it does not pick up the
pattern. The numbers I'm adding from the 4 sheets have 4 blank cells in
between. for example =Sum(sheet 1:sheet4 A2!) and the next one is
=Sum(sheet1:sheet4 A6!).

Is there a combination I could use so that it skips 4 cells and then adds it?

thanks,

 

[Don Guillett]

try incorporating this idea into your formula (starting with the second one)
=INDIRECT("g"&ROW(C1)*5)

 

[Guest]

Hi Don,

I dont understand how to add your addition to my formula. Right now I have
=sum(sheet1:sheet4:!a6) should i add your like this: =sum(sheet1:sheet4:!a6,
=INDIRECT("g"&ROW(C1)*5).

thanks,

 

[Guest]

All I want to do is tell Excel to sum the next 4 numbers when I use Auto Fill
to copy the original Formula which is =Sum(Sheet1:Sheet4!A1)

Any Suggestions?

Thanks,

 

[Don Guillett]

Try it this way for the 2nd set -copied down. This is ONE line so correct
for word wrap. Send me your email address and I will send you a sample
workbook.

=SUM(SUMIF(INDIRECT("Sheet"&{2,3,4}&"!A"&ROW(A1)*4),"<>0",INDIRECT("Sheet"&{2,3,4}&"!a"&ROW(A1)*4)))