Microsoft Office Tutorials and References
In Depth Information
Multisheet Functions
You may need to create a function that works with data contained in more than one worksheet within a work-
book. This section contains two VBA custom 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.
This topic's website contains the workbook multisheet functions.xlsm that demon-
strates 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 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 _ Search JabSto ::

Custom Search