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

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.