Indexing a formula from Sheet to Sheet

[mrudnet]

I have 52 tabs (one for each week of the year). I want the formula to index
from one sheet to the next so that I get a cumulative total. The formula
does not seem to index; instead, it just copies over. How do you do this?

 

[Max]

I may not have any suggestion to offer here but I think it would help
attract responses if you could clarify what you're trying to do. Post your
attempted formula, some sample data, the expected results, how your 52 tabs
are named, etc.

 

[Herbert Seidenberg]

Starting on sheet "Wk02"
enter at B1
=INDIRECT("Wk"&TEXT(RIGHT(CELL("filename",A1),2)-1,"00")&"!B1")+A1

 

[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 52 sheets, sheet1 through sheet52...........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