Sum Function
 

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.

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.

Sum Function
 

Let's say that in each sheet the monthly value is in B9 and we want the cumm
to date in B10.

In Feb sheet in cell B10 enter:
=B9+Jan!B10

In the Mar sheet in B10 enter:
=B9+Feb!B10

etc.


B10 builds cummulatively month-by-month

--
Gary''s Student - gsnu200772


"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.