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)

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 it 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, there's no 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 inside the loop performs a check to see 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

This function can be modified easily to perform other cross-worksheet calculations, such as minimum, average, sum, and so on.

0 0

Post a comment