monthly cumulative worksheets

[Pollyanna]

I am setting up monthly worksheets in same workbook April 08 - March 09.
The data includes Staff extra hours and staff days off sick.
For the extra hours, I want each month to reference the total for last
month: I can do this manually (using [=]) but I would like a way of doing it
once and copying to the other 11 worksheets.
For the days off sick, I would like each month to show the cumulative total
for the last x months - so June's sheet would show total April + May. Again I
can do this manually but is there a quicker way?

 

[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 12 sheets, sheet1 through sheet12...........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

 

[Pollyanna]

Thanks very much. I will try this!

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 12 sheets, sheet1 through sheet12...........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

I am setting up monthly worksheets in same workbook April 08 - March 09.
The data includes Staff extra hours and staff days off sick.
For the extra hours, I want each month to reference the total for last
month: I can do this manually (using [=]) but I would like a way of doing it
once and copying to the other 11 worksheets.
For the days off sick, I would like each month to show the cumulative total
for the last x months - so June's sheet would show total April + May. Again I
can do this manually but is there a quicker way?