Recording Historic Trend Data
 

Without code, I think the formula solution you seek would be
cumbersome to keep the historic values and get the new values with the
same formula. You might be better off with a subroutine that you
could run whenever the sheet was opened or whenever the values change:
Dim sht As Worksheet
Dim x As Integer
Set sht = ActiveSheet
For x = 4 To 7
If Date = DateValue(sht.Cells(1, x).Value) Then
sht.Cells(2, x).Value = sht.Cells(2, 2).Value
sht.Cells(3, x).Value = sht.Cells(3, 2).Value
sht.Cells(4, x).Value = sht.Cells(4, 2).Value
End If
Next x
Set sht = Nothing