Looking for the Elegant solution

[Howard Laube]

I use a "RTD" link to calculate a value. As the data changes over time, I want to save the Largest and the smallest value in another cell.

Is there a simple way to record the largest or smallest value from the real time data? ...Replace if greater function?

 

[Tom Urtis]

Elegance being in the eye of the beholder, if it were me, because you said "I use a "RTD" link to calculate a value", I'd use a VBA sheet level Calculate event to maintain a static min and max.

You did not give any range references, so suppose your numbers being calculated by formulas are in column B (column 2 to Excel).

Further suppose you want the lowest ever minimum in cell D1 and the highest ever maximum in cell E1.

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Calculate()
With WorksheetFunction
Range("D1").Value = .Min(Range("D1").Value, .Min(Columns(2)))
Range("E1").Value = .Max(Range("E1").Value, .Max(Columns(2)))
End With
End Sub

Remember, it assumes you have formulas in column B as I rely on your words that you are calculating values from a link. If this is a manual or paste change being made in column B, then use this procedure *instead*:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
With WorksheetFunction
Range("D1").Value = .Min(Range("D1").Value, .Min(Columns(2)))
Range("E1").Value = .Max(Range("E1").Value, .Max(Columns(2)))
End With
End Sub