• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Record dynamic cell value with date/time stamp and then use lookup

Sam Longstaff

New Member
I have a worksheet that has a dynamic cell (A1) with a constantly changing value. I want to record a history of this value and the time and date that it has changed. I have found some VBA solutions online that can do a time/date stamp or a list history of the value but not a VBA solution that does both. So ideally I would have:

A1 - Dynamic value
B1 - First historic value of A1, C1 - Date/time stamp
B2 - Second historic value of A1, C2 - date/time stamp
and so on.

Once I have this list I would then wish to be able to lookup the list so that I could get the historic value of A1 at the end of each month and record this so that it can feed in to a performance dashboard. I did have an array formula solution that was initially working but this formula was made using indirect and then became volatile.

I hope someone can help. Thank you.



You can use a worksheet change event to record the data each time the value is changed. You should be able to search this forum that may have the answer you seek.


Here' a code that you can look at.
In the worksheet for the amount you will need a header. Also a header for column C.
Copy code to the worksheet that you are using.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
Dim lrow As Long
If target.Address <> "$A$2" Then Exit Sub
'' get last row in column ''
lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Row + 1''' change sheet1 to your sheet
Application.EnableEvents = False
Sheets("Sheet1").Range("C" & lrow).Value = Format(Now(), "yyyy-MM-dd hh:mm:ss")
Application.EnableEvents = True
End Sub
Last edited:

Sam Longstaff

New Member
Thanks for your response Charles. It is recording a time stamp but not a historical record of the dynamic cell value with a timestamp next to it. Any ideas?