Sum Function
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 Mon, 10 Mar 2008 16:46:00 -0700, Roger
wrote:
I have 12 different worksheets for each month in a year in one workbook. In
each month's worksheet, I want year-to-date figures up to that month. All
the worksheets have the same fields in same cells. I prepare one worksheet
with all data and formulas for one month. Then I name the worksheet "Jan"
for January and copy that worksheet 11 times and rename them according to
individual months.
How can I insert a common formula that will work in every worksheet for
getting the year-to-date figures in each month's worksheet?
Thanks in advance for your time.
|