How do I reference the same cell in all previous sheets?

[Joe Lewis]

I want to refence a cell in all previous sheets in a workbook. The problem is
that I'd like to paste the formula to other sheets and still have it refernce
all previous sheets to that sheet.

For example, on sheet 5 the formula would refence cell A1 in sheets 1, 2, 3,
and 4. When I copy and paste the formula to a cell in sheet 9, I want it to
automatically expand the sheet range to now reference A1 in sheets 1, 2, 3,
4, 5, 6, 7, and 8.

Is this possible?

 

[Gord Dibben]

Copy/paste this UDF to a general module in your workbook.

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

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

 

[Joe Lewis]

Thank you so much. Will this only reference that cell for the previous
worksheet, or will it reference that cell on all worksheets previous to the
current one?

The reason I ask, is that I want to have a "year-to-date" statistics section
on each page that draws information from all previous worksheets to the
current one.

That way at the end of the year, I can go back through the pages and see the
year-to-date stats changing over time.

 

[Gord Dibben]

It will reference the cell on the previous sheet only.

But make it reference a cell on each previous sheet that accumulates from
sheet to sheet to sheet.


Gord