Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
monthly cumulative worksheets
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
monthly cumulative worksheets
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 On Wed, 23 Apr 2008 04:18:01 -0700, Pollyanna wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
monthly cumulative worksheets
Thanks very much. I will try this!
"Gord Dibben" wrote: 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 On Wed, 23 Apr 2008 04:18:01 -0700, Pollyanna wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rolling Monthly Amounts to Annual Monthly Amounts | Excel Discussion (Misc queries) | |||
How do I set up monthly random work schedule for 60 hours monthly | Excel Discussion (Misc queries) | |||
Need formula for cumulative interest with additional monthly princ | Excel Discussion (Misc queries) | |||
create yearly summary from monthly worksheets | Excel Discussion (Misc queries) | |||
Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) |