Record Number of sum to formula based cell - Excel

Sunil

In my answer below, I've made some guesses based on your question. I've assumed this is linked to a remote file (as per the link in the Discussion to your question) and that nothing need to happen to your "positive" and "negative2 value (in E and F) if the rate stays unchanged.

I've left your VLOOKUP formula in place (in columns Cand D of Sheet2 of the attached .xlsm file) but in green cells (rows 17 and 18), I changed the reference to the revised remote .xlsx file, Remote data link for Sum (1).xlsx (for test purposes- you'd need to re-make the VLOOKUP references for those to get it to work).

The code beow does what your question requests (I believe) and is commanted for your convenience. Chnages in the remite file with trigger the Worksheet_Calculate event and cause this macro to run (and update sheet 1 of the .xlsm file):

Private Sub Worksheet_Calculate()

Dim Rpt As Range, Cll As Range

' prevent retriggering
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'run through cells and modify Report if value has changed
For Each Cll In Sheet2.Range("C3:C25")
    If IsNumeric(Cll.Value) Then

        Set Rpt = Sheet1.Range(Cll.Address)

        ' compare new values with those in Rpt
        Select Case Cll.Value - Rpt.Value
            Case Is > 0 ' write to E
                Rpt.Offset(0, 2).Value = Rpt.Offset(0, 2).Value + Cll.Offset(0, 1).Value
            Case Is < 0 ' write to F
                Rpt.Offset(0, 3).Value = Rpt.Offset(0, 3).Value + Cll.Offset(0, 1).Value
            Case Is = 0
                ' Do nothing not sure what happens here!!
            Case Else
                ' ### for errors?
        End Select
        'write new values to B, C and D
        Rpt.Offset(0, -1).Value = Cll.Offset(0, -1).Value
        Rpt.Value = Cll.Value
        Rpt.Offset(0, 1).Value = Cll.Offset(0, 1).Value
    End If
Next Cll
' restore conditions
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hope this helps.