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?

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

On Sun, 23 Nov 2008 19:39:00 -0800, Joe Lewis
wrote:

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?

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" wrote:

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

On Sun, 23 Nov 2008 19:39:00 -0800, Joe Lewis
wrote:

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?

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

On Sun, 23 Nov 2008 21:08:01 -0800, Joe Lewis
wrote:

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" wrote:

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

On Sun, 23 Nov 2008 19:39:00 -0800, Joe Lewis
wrote:

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?