With this set, The Max formula should reference the cell(s) you want to monitor, and itself.

Eg if your Max is in D3 in Sheet1 and is monitoring B3 on Sheet2 then use

=MAX(Sheet2!B3,D3)

Note: stored Max value will survive the workbook being closed and reopened.

CodePudding user response：

Thic can be achieved with a volitile UDF, using a Static variable to remember the last Max value

Function MyMax(r As Range) As Variant
    Application.Volatile True
    Static LastMax As Variant
    Dim NewMax As Variant
    NewMax = Application.WorksheetFunction.Max(r)
    If NewMax > LastMax Then
        LastMax = NewMax
    End If
    MyMax = LastMax
End Function

Note:

1. stored Max value won't survive the workbook being closed and reopened. To achieve that you'd need to store the value somewhere else, eg in an out of the way cell
2. This formula only stores one Max value. If you want to use the formula >1 times in your workbook, each with its own Max, you'll need to store each Max seperately, perhaps in a Collection