Excel Developer Tip

A Custom Function For Relative Sheet References

You may have discovered that Excel's support for "3D workbooks" is limited. For example, if you need to refer to a different worksheet in a workbook, you must include the worksheet's name in your formula. This is not a big problem -- until you attempt to copy the formula across other worksheets. The copied formulas continue to refer to the original worksheet name.

This tip contains a VBA function (named SHEETOFFSET) that lets you address worksheets in a relative manner. For example, you can refer to cell A1 on the previous worksheet using this formula:

=SHEETOFFSET(-1,A1)

Then, you can copy this formula to other sheets and the relative referencing will be in effect in all of the copied formulas.

The SHEETOFFSET Function

The VBA code for the SHEETOFFSET function is listed below.

Function SHEETOFFSET(offset, Ref)
'   Returns cell contents at Ref, in sheet offset
    Application.Volatile
    With Application.Caller.Parent
        SHEETOFFSET = .Parent.Sheets(.Index + offset) _
         .Range(Ref.Address).Value
    End With
End Function

Using the SHEETOFFSET function

To use this function in a worksheet, just copy the code and paste it to a VBA module. Then, you can use formulas such as:

=SHEETOFFSET(2,C1)

The first argument represents the sheet offset, and it can be positive, negative, or 0.
The second argument must be a reference to a single cell. If the first argument is 0, the cell reference must not be the same as the cell that contains the formula. If so, you'll generate a circular reference error.

NOTE:
Be careful if your workbook contains non-worksheet sheets (for example, chart sheets). If the offset argument results in a reference to a chart sheet, the function will display an error.

Thanks to Bill Manville for suggesting a correction to the original function posted here.