• 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

    Hui...

  • 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.
 

charlesdh

Member
Hi,

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.
 

charlesdh

Member
Hi,
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.

Code:
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?
 
Top