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
In the light brown input area enter words (or numbers). Those words will be stored in column F. If you type over a word the original word is still stored in column F (in the same order) and the new word is added further down.
Here’s the code:
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
My name is Kevin Lehrbass. I’m a Data Analyst from Markham Ontario Canada.
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.