This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
While you can reference other sheets in XL formulae
=SUM(Sheet1!A1:A100)
the reference must be to a specific sheet. In some cases the need may arise to refer to a previous sheet by position, rather than by name. For instance, if Sheet3 is inserted between Sheet1 and Sheet2, you may want the reference to refer to Sheet3, rather than Sheet1.
This UDF will return a value from the previous sheet (working left to right). There are two calling methods. The first, with no arguments
=PrevSheet()
will refer to the cell(s) in the previous sheet with the same address as the cell(s) it's entered in.
The second method refers to whichever cells in the previous sheet are referenced by the argument:
=SUM(PrevSheet(A1:A100))
If the formula is entered in the left-most sheet, the UDF will returnt the #REF! error.
'******************************************************
'Purpose: Reference cell(s) on the previous sheet
'Inputs: rRng - optional range reference
'Returns: Range with same address as rRng on the sheet
' to the left of the calling sheet. If rRng not
' provided, the calling cell reference is used
'******************************************************
Public Function PrevSheet( _
Optional rRng As Excel.Range) As Variant
Dim nIndex As Integer
Application.Volatile
If rRng Is Nothing Then Set rRng = Application.Caller
nIndex = rRng.Parent.Index
If nIndex > 1 Then
Set PrevSheet = Sheets(nIndex- 1).Range(rRng.Address)
Else
PrevSheet = CVErr(xlErrRef)
End If
End Function
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros
About Event Macros>