Copying worksheets?

[KellyLC]

Is it possible to automatically change a specific formula which references
the previous worksheet? example:
Day 1 is subtracted from the Day 2 total, when I copy the sheet to name it
Day 3, I want the formula to subtract Day 2 from Day 3. I have to make 31
copies for each day of the month. Is there an easier way to make the change

 

[Luke M]

The first task is to create a formula that can detect the number of the day
of your sheet.
=RIGHT(CELL("filename",A1),1)
and the corresponding sheet to subtract will then be
=RIGHT(CELL("filename",A1),1)-1

To use this information in a formula, we can use the INDIRECT function,
which combines text and formulas to create references. Assuming you are
dealing with cell A2 in each respective worksheet:

=A2-INDIRECT("'Day "&RIGHT(CELL("filename",A1),1)-1&"'!A2")

Placing this formula in sheet Day 3 would create a formula equivalent to:
='Day 3'!A2 - 'Day 2'!A2

Notes:
Do NOT change "filename" to your actual file name. This is part of the
formula structure.
Pay close attention to the placement of double and single quotes within the
INDIRECT function.
Within the CELL function, it does not matter what cell you reference.

 

[Gord Dibben]

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

Function PrevSheet(rg As Range)
'accounts for more than one workbook open
'and has hidden sheets
'Bob Phillips October 4, 2009
Dim N As Variant
With Application.Caller.Parent
N = .Index
Do
If N = 1 Then
PrevSheet = CVErr(xlErrRef)
Exit Do
ElseIf TypeName(.Parent.Sheets(N - 1)) <> "Chart" And _
.Parent.Sheets(N - 1).Visible = xlSheetVisible Then
PrevSheet = .Parent.Sheets(N - 1).Range(rg.Address).Value
Exit Do
End If
N = N - 1
Loop
End With
End Function

In Sheet2 enter =prevsheet(A1) which refers to Sheet1 .

When you copy Sheet2 to name it Sheet3 then Sheet2 will become the "previous
sheet"


Gord Dibben MS Excel MVP