Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()
MultiCat()
PageSetupXL4M()
PrevSheet()
RandInt()
SampleNoReplace()
SumBold()
UnixToXLTime()

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Reference previous sheets

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

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros

Where to put your code

About Event Macros>