Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
This formula returns the maximum value in cell B1 for Sheet1 , Sheet4 , and all the sheets in
between.
But what if you add a new sheet ( Sheet5 ) after Sheet4 ? Your formula won’t adjust
automatically, so you need to edit the formula to include the new sheet reference:
=MAX(Sheet1:Sheet5!B1)
The MaxAllSheets function, which follows, accepts a single-cell argument and returns the
maximum value in that cell across all worksheets in the workbook. The formula that follows, for
example, 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)
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 Wksht.Name = cell.Parent.Name And _
Addr = Application.Caller.Address Then
‘ avoid circular reference
Else
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 = 0
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
 
Search JabSto ::




Custom Search