How to easily apply formula across more than 1 worksheet?

[Special K]

I have the following formula:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))

I will need to apply this entire formula to other sheets besides Sheet1 and sum the totals, similar to this:

=SUM(

SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))

SUM((Sheet2!$E3:$E65535)*(Sheet2!$H3:$H65535=$A$2)*(Sheet2!$I3:$I65535=A7)*(Sheet2!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet2!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))

SUM((Sheet3!$E3:$E65535)*(Sheet3!$H3:$H65535=$A$2)*(Sheet3!$I3:$I65535=A7)*(Sheet3!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet3!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))

)

I will need to expand this formula over time as more sheets are added. Also the names of the sheets will not be Sheet1, Sheet2, etc. and will not follow any sort of pattern. Also the formula will be used in each cell of a large table with variations in the DATE ranges.

Is there any way to take the base equation, some sort of FOR loop, and a list of the sheet names to make the implementation easier? That way when a new sheet is added all I have to do is add it to the list of sheets and all of the cells will update their formulas automatically?

 

[ukmikeb]

Hi

Considering this topic is also linked to -
http://www.mrexcel.com/forum/showthread.php?t=561350

may I suggest that you clarify your objectives and provide a sample of your data.

I think it is quite possible for you to approach this from another direction and use other functions (eg SUMPRODUCT) to reach your goal.

Also, assuming you are using Excel 2003, does your data really occupy the whole column?

hth