Is that VB Code at the bottom of your message? Do I just copy and paste that
into VB? Do I need to reference my worksheet names somewhere? They are
called Pay Period 6, Pay Period 7, Pay Period 8, etc. I tried putting
PrevSheet(F4) into my second worksheet, but it didn't populate with the
previous worksheet's data.

Susan

"Gord Dibben" wrote:

Susan

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
Application.Volatile

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


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 08:20:02 -0700, Susan
wrote:

How can I reference a worksheet tab in a formula, so that it always refers to
the previous tab in a workbook, such as a formula in Tab 3 needs info from
the same cell in Tab 2 in order to keep track of balances. When I copy and
paste the spreadsheets into a new worksheet it keeps the reference that was
in the original worksheet.

Susan