Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

You may need to create a function that works with data contained in more than one worksheet within a workbook. This section contains two VBA functions that enable you to work with data across multiple sheets, including a function that overcomes an Excel limitation when copying formulas to other sheets.

Returning the Maximum Value Across All Worksheets

If you need to determine the maximum value in a cell (for example, B1) across a number of worksheets, use a formula like this one:

=MAX(Sheet1:Sheet4!B1)

This formula returns the maximum value in cell B1 for Sheet1, Sheet4, and all of the sheets in between. But what if you add a new sheet (Sheet5) after Sheet4? Your formula does not adjust automatically, so you need to edit it to include the new sheet reference:

=MAX(Sheet1:Sheet5!B1)

The following function accepts a single-cell argument and returns the maximum value in that cell across all worksheets in the workbook. For example, the following formula returns the maximum value in cell B1 for all sheets in the workbook:

=MAXALLSHEETS(B1)

If you add a new sheet, you don't need to edit the formula.

Function MAXALLSHEETS(cell as Range) As Variant Dim MaxVal As Double Dim Addr As String Dim Wksht As Object Application.Volatile Addr = cell.Range("A1").Address MaxVal = -9.9E+307 For Each Wksht In cell.Parent.Parent.Worksheets If Not Wksht.Name = cell.Parent.Name Or _ Not Addr = Application.Caller.Address Then If IsNumeric(Wksht.Range(Addr)) Then If Wksht.Range(Addr) > MaxVal Then _ MaxVal = Wksht.Range(Addr).Value End If End If Next Wksht If MaxVal = -9.9E+307 Then MaxVal = CVErr(xlErrValue) MAXALLSHEETS = MaxVal End Function

The For Each statement uses the following expression to access the workbook:

cell.Parent.Parent.Worksheets

The parent of the cell is a worksheet, and the parent of the worksheet is the workbook. Therefore, the For Each-Next loop cycles among all worksheets in the workbook. The first If statement inside the loop checks whether the cell being checked is the cell that contains the function. If so, that cell is ignored to avoid a circular reference error.

The SHEETOFFSET Function

A recurring complaint about Excel (including Excel 2007) is its poor support for relative sheet references. For example, suppose that you have a multisheet workbook, and you enter a formula like the following on Sheet2:

=Sheet1!A1+1

This formula works fine. However, if you copy the formula to the next sheet (Sheet3), the formula continues to refer to Sheet1. Or if you insert a sheet between Sheet1 and Sheet2, the formula continues to refer to Sheet1, when most likely, you want it to refer to the newly inserted sheet. In fact, you can't create formulas that refer to worksheets in a relative manner. However, you can use the SHEETOFFSET function to overcome this limitation.

Following is a VBA Function procedure named SHEETOFFSET:

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).Value Exit Function Else WksNum = WksNum + 1 End If Next wks End Function

The SHEETOFFSET function accepts two arguments:

• offset: The sheet offset, which can be positive, negative, or 0.

• cell: (Optional) A single-cell reference. If this argument is omitted, the function will use the same cell reference as the cell that contains the formula.

For more information about optional arguments, see the section, "Using Optional Arguments," later in this chapter.

The following formula returns the value in cell A1 of the sheet before the sheet that contains the formula:

=SHEETOFFSET(-1,A1)

The following formula returns the value in cell A1 of the sheet after the sheet that contains the formula:

=SHEETOFFSET(1,A1)