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:
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:
- 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.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks