Multisheet Functions


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.

On the CD 

The companion CD-ROM contains the workbook image from book multisheet functions.xlsm that demonstrates the multisheet functions presented in this section.

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.

Note 

You can easily modify the MAXALLSHEETS function to perform other cross-worksheet calculations: Minimum, Average, Sum, and so on.

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) 




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net