copying & averaging formulas

[Guest]

I hope you can help me.
I keep a monthly spreadsheet of sales numbers. each month has its own tab.
I have an average ytd column that takes the average from each subsequent
month, then adds to the current month to come up with a YTD average. As the
months go on this gets bigger and more difficult to manage.

here is an example of one formula =('Jan 07 Pace Final'!E7+'Feb 07 Pace
Final'!E7+'Mar 07 Pace Final'!E7+'Apr 07 Pace Final'!E7+'May 07 Pace
Final'!E7+E7+'June 07 Pace Final'!E7+'July 07 Pace'!E7)/('Jan 07 Pace
Final'!B7+'Feb 07 Pace Final'!B7+'Mar 07 Pace Final'!B7+'Apr 07 Pace
Final'!B7+'May 07 Pace Final'!B7+B7+'June 07 Pace Final'!B7+'July 07 Pace'!B7)

is there any way to make this more efficient and easier to use?
thanks for all your help in advance!

 

[JE McGimpsey]

One way:

=SUM('Jan 07 Pace Final:July 07 Pace'!E7)/SUM('Jan 07 Pace
Final:July 07 Pace'!B7)

 

[Ragdyer]

One way:

=SUM('Jan 07 Pace Final:Jul 07 Pace Final'!E7)/SUM('Jan 07 Pace Final:Jul 07
Pace Final'!B7)

Another way is to insert a couple of blank "Dummy" sheets at the beginning
and end of your sheet tabs, and name them:
Start
and
End

They should "sandwich" *ALL* the sheets that you wish to include in the
calculations,
THEN ... the formula becomes:

=SUM(Start:End!E7)/SUM(Start:End!B7)

 

[JE McGimpsey]

Note that 'Jan 07 Pace Final' sheet tab would need to be on the left and
the 'July 07 Pace' sheet tab on the right, with the listed sheet tabs in
between.

 

[Ragdyer]

I'm pretty sure John, that XL will automatically put them in the correct
order for you.

 

[Guest]

Hi and thanks for your input. That would help but the problem is that the
cell ref number changes frequently. So while user 1 might be in E6 for jan,
feb and march, he might then be in E7 for april and May and so on.
Every month i copy the spreadsheet forward then I have to check every entry
to make sure the formula did not change during copying.

 

[Ragdyer]

Have you given any thought to using templates, so that everything would be
consistent?