Excel is not meant to store historical data. That’s what databases do. However, Tom Urtis shows us a trick to keep the all time min value from a single input cell.
(Post concept from Tom Urtis. Visit Tom’s blog)
What Does It Do?
In rare cases this could be extremely helpful. Instead of using vba to store all values and then use MIN and MAX functions you could use Tom’s vba solution.
(download my Excel file)
I modified Tom’s code a bit to also include an all time maximum (along with all time minimum).
If .Address <> “$A$2” Then Exit Sub
If Len(.Value) = 0 Then
If .Value < Range(“B2”).Value Then Range(“B2”).Value = .Value
If .Value > Range(“C2”).Value Then Range(“C2”).Value = .Value
Dim nextrow As Integer, Savetxt As Range
Set Savetext = Range(“A1:D5”)
nextrow = Application.WorksheetFunction.CountA(Savetext)
If Target.Column > 4 Then Exit Sub
If Target.Row > 4 Then Exit Sub
About Tom Urtis
He’s an Excel consultant, Excel MVP, and Excel legend! Tom is also a big sports fan (baseball and football). Visit his blog: https://www.atlaspm.com/
In these difficult times of Covid-19 I’m keeping my mind occupied here at home by playing around in Excel and Power BI. Wait…I always do that! I’ve also calculated the optimal balance of news and comedy. Oh yeah…these are my dogs Fenton and Cali. Starting yesterday the neighbourhood kids aren’t running around outside so they have nothing to bark at.