help with previous sheet reference formula

[tj]

Subject: excel formula
From: "TJ" <[email protected]> Sent:
4/28/2004 11:30:51 AM




trying to create a payroll spreadsheet using "sheets" as
payperiods. Having trouble creating incrimental increase
of accrued Vacation Sick and Comp without re-entering
data on each sheet. need a formulas for the following:

"previous sheet value" + or - "Accrued time" equals new
value

I have 26 Sheets representing pay periods. I need a
"previous sheet reference" instead of a specific sheet
reference. Please help

 

[Gord Dibben]

tj

Copy/paste this User Defined Function to a general module in your workbook.

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
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

If not familiar with VBA and macros see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP