Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
The SheetOffset function
You probably know that Excel’s support for 3-D 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. Adding the worksheet name isn’t a big problem . . . until you attempt to copy the formula
across other worksheets. The copied formulas continue to refer to the original worksheet name,
and the sheet references aren’t adjusted as they would be in a true 3-D workbook.
The example discussed in this section is a VBA function (named SheetOffset ) that enables
you to address worksheets in a relative manner. For example, you can refer to cell A1 on the
previous worksheet by using this formula:
=SheetOffset(-1,A1)
The first argument represents the relative sheet, and it can be positive, negative, or zero. The
second argument must be a reference to a single cell. You can copy this formula to other sheets,
and the relative referencing will be in effect in all the copied formulas.
The VBA code for the SheetOffset function follows:
Function SheetOffset(Offset As Long, Optional Cell As Variant)
‘ Returns cell contents at Ref, in sheet offset
Dim WksIndex As Long, WksNum As Long
Dim wks As Worksheet
Application.Volatile
If IsMissing(Cell) Then Set Cell = Application.Caller
WksNum = 1
For Each wks In Application.Caller.Parent.Parent.Worksheets
If Application.Caller.Parent.Name = wks.Name Then
SheetOffset = Worksheets(WksNum + Offset).Range(Cell(1).Address)
Exit Function
Else
WksNum = WksNum + 1
End If
Next wks
End Function
Returning the maximum value across all worksheets
If you need to determine the maximum value in cell B1 across a number of worksheets, you would
use a formula such as this:
=MAX(Sheet1:Sheet4!B1)
 
Search JabSto ::




Custom Search