link to the pervious sheet

[Girish Tare]

Hi,

I want an excel formula to work automatically in hte way that if say i have
2 tabs in a workbook; Sheet1 & Sheet2 and there is a formula in sheet2 that
is capturing value from sheet1. But if i create a copy of sheet2 as sheet3;
the formula in sheet3 should automatically capture data from sheet2.

e.g.: here is a formula in sheet2 saying "='sheet1'!AA1+AA1" and if i copy
sheet2 as sheet3 in the same workbook the formula in sheet3 should get
convertered to "='sheet2'!AA1+AA1".

I tried this lot of times but couldn't get the answer.

 

[Bernie Deitrick]

Girish,

You need to use a user-defined-function, like:

Function Prev(R As Range) As Variant
Dim ShtNum As Integer

ShtNum = Application.Caller.Parent.Index
If ShtNum = 1 Then
Prev = 0
Else
Prev = Sheets(ShtNum - 1).Range(R.Address).Value
End If

End Function

used like

=Prev(AA1)

HTH,
Bernie
MS Excel MVP