Excel maximum minimum value from a range across multiple sheets

You can create a 'first' worksheet at the beginning and hide it. Repeat the process with a 'last' worksheet at the end of the series. The formula becomes,

=max(first:last!f5:f39004)

Solved both issues of looping through all sheets and selecting the range/column. I created 2 functions (functions can be found below) one for the minimum and the other for the maximum. You only need to select the range as demonstrated in the picture below. Both function work even if you have blanks or text in the selected range across sheets, but if you have error they do not work.

To be able to use these functions. You need to copy past the functions source code (found below) in a module. The functions will be available only in the workbook you copied the functions to.

If you want the functions to be available to any workbook you open. You need to save the workbook that contains the function as an add in, then activate the add in. This is a really simple step Click Here to see how to do the add in thing

Note, if you type the functions say in "sheet1" Then you go to a different sheet say "sheet2" and change the numbers the functions will not automatically calculate. You need to go to the formula bar and press enter. If the number you change is in the same sheet as the sheet you typed the functions. It updates automatically

Minimum Across Sheets Function

Public Function Minimum_Across_Sheets(rngSelection As Range) As Double

Dim dMinimum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets

    For Each rng In rngSelection

        If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then

            If Not bFirst_Value_Obtained Then
                dMinimum_Value = wks.Cells(rng.Row, rng.Column)
                bFirst_Value_Obtained = True
            End If

            If wks.Cells(rng.Row, rng.Column) < dMinimum_Value Then
                dMinimum_Value = wks.Cells(rng.Row, rng.Column)
            End If

        End If

    Next rng

Next wks

Minimum_Across_Sheets = dMinimum_Value
End Function

Maximum Across Sheets Function

Public Function Maximum_Across_Sheets(rngSelection As Range) As Double

Dim dMaximum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets

    For Each rng In rngSelection

        If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then

            If Not bFirst_Value_Obtained Then
                dMaximum_Value = wks.Cells(rng.Row, rng.Column)
                bFirst_Value_Obtained = True
            End If

            If wks.Cells(rng.Row, rng.Column) > dMaximum_Value Then
                dMaximum_Value = wks.Cells(rng.Row, rng.Column)
            End If

        End If

    Next rng

Next wks

Maximum_Across_Sheets = dMaximum_Value
End Function

Hope this is useful to you.