Forum Discussion

alexgibson01's avatar
alexgibson01
Copper Contributor
Apr 10, 2025

Automatically record the value of one cell into a different cell each day

Each day I record the current value of my shares into cell B2 on Sheet1. Below, in B5, C5 etc I have all future dates for the next few years in 1 row. In cell C2 I have todays' date (=TODAY()). How can I automate excel to record the last value of cell B2 every day into a different cell under the dates. So far I've got =IF(B5=$C$2,$B$2,""). So, if false, I want to keep the last value of B2. Essentially a basic database that I can use to create an ongoing graph.

excel
  • alexgibson01's avatar
    alexgibson01
    Copper Contributor
    Apr 22, 2025

    Thank you very much for your help, it's really appreciated. Even though I don't know any VBA, I'm hoping to try and implement this solution very soon. 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 11, 2025

    To automatically "lock in" today's value of B2 under the matching date (and keep previous values), you need to use VBA. Unfortunately, formulas alone cannot create a "persistent history" like this—they can only display live values.

    Here is a small example with VBA.

    Sub LogShareValue()
    Dim ws As Worksheet
    Dim todayDate As Date
    Dim lookupRange As Range
    Dim cell As Range
    Dim targetRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    todayDate = Date ' Today's date
    targetRow = 3 ' Row number where values will be stored (under your dates)

    ' Assume dates are in Row 5 starting from Column B
    Set lookupRange = ws.Range("B5", ws.Cells(5, ws.Columns.Count).End(xlToLeft))

    For Each cell In lookupRange
        If cell.Value = todayDate Then
            If ws.Cells(targetRow, cell.Column).Value = "" Then
                ws.Cells(targetRow, cell.Column).Value = ws.Range("B2").Value
            End If
            Exit For
        End If
    Next cell
End Sub

    you can also add it to the Workbook_Open() event:

    Private Sub Workbook_Open()
    Call LogShareValue
End Sub

    Example file is attached.

    Hope this helps you.

Resources