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?

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mrudnet" wrote in message
...
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?

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

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


On Wed, 28 Nov 2007 06:44:12 -0800, mrudnet
wrote:

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?