formula problem in grouped worksheets

[Bo]

Excel 2003

I have 99 tabbed worksheets in one workbook. All 99 have the same template.
Each template collects a weeks worth of data and calculates a year to date
column. I have grouped sheets 2-99 and on the active sheet 2 entered the
following formula.

=+Sheet1!E13+Sheet2!D13

Sheet1 E13 being the previous week year to date. Sheet2 D13 being this weeks
total.

Problem: The formula in all sheets 3-99 always looks at the data in Sheet1
and Sheet2 as if the formula read =+$Sheet1$E13+$Sheet2$D13. How do you make
it quit.

 

[Gord Dibben]

Sounds like you want a progressive accumulation across sheets.

If you're willing to use a User Defined Function this becomes quite
easy.......

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

Example of use......................

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