Cumulative totals carrying over to several spreadsheets

[Guest]

I'm creating an Excel spreadsheet with separate tabs for each week of the
year. I need to have cumulative totals carrying over from one week to the
next through the end of the year. Is it possible to do this?

 

[Stephen]

I'm creating an Excel spreadsheet with separate tabs for each week of the
year. I need to have cumulative totals carrying over from one week to the
next through the end of the year. Is it possible to do this?

Yes. What's your problem with this? Just add the previous week's cumulative
total and the current week's total to get the new cumulative total.

 

[Guest]

I'm sorry. I stated my question wrong. I really need to know if FORMULAS
will carry over across several tabs. My spreadsheets are going to have many
cum totals, and it would be too time-consuming to just add.

 

[Peo Sjoblom]

I would put 2 dummy sheets first and last and then have all the other sheet
in-between, then you can delete or add sheets to your liking


=SUM(First:Last!A31)

will sum A31 for all the sheets between First and Last


otherwise replace First with the first sheet you want to include and Last
with the last sheet


--


Regards,


Peo Sjoblom

 

[Gord Dibben]

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Say you have 52 sheets, sheet1 through sheet52...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP


On Thu, 15 Nov 2007 08:46:01 -0800, Schotze's Mom <Schotze's