update numerical value automatically based on change in another

il38694 

Do you want B1 to keep track of the highest value in A1? That requires a bit of VBA code.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Change range as needed, for example to A1:A20
    Const MyRange = "A1:A1"
    Dim rng As Range
    If Not Intersect(Range(MyRange), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        On Error GoTo ExitHere
        For Each rng In Intersect(Range(MyRange), Target)
            If rng.Value <> "" Then
                If IsNumeric(rng.Value) Then
                    If rng.Offset(0, 1).Value = "" Or rng.Value > rng.Offset(0, 1).Value Then
                        rng.Offset(0, 1).Value = rng.Value
                    End If
                End If
            End If
        Next rng
ExitHere:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Change the range as needed.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.