This is a User Defined Function and no, you don't need to reference your sheet
names.

Have your workbook open.

Hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Right-click on your workbook/project and InsertModule

Paste the code into that module.

Then Alt + q to return to your worksheet.

In Pay Period 7 sheet F4 enter =PrevSheet(F4)

That will give you Pay Sheet 6 F4 contents.

A quick way to enter on all sheets at once is to select all but first sheet then
in F4 of active sheet enter the above formula which will be entered in all
grouped sheets.


Gord

On Thu, 5 Jul 2007 10:06:03 -0700, Susan
wrote:

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