How to record Value based on condition - Excel

Sunil

Happy New Year to you and all Forum users!

In the FIRST attached revised file:

1. the sherts are renamed to Transactions (linked to the remote file) and Totals- for the VBA-summed values) 
2. the VBA ThisWorkbook section has this code (with commnets to show how it works):

Dim LstRw As Long, LastVals As Variant

Private Sub Workbook_Open()

    With Sheet1
        ' capture depth of sheet (col B) and current Trade values (col D)
        LstRw = .Range("B" & Rows.Count).End(xlUp).Row
        LastVals = .Range("D1:D" & LstRw).Value
    End With
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim Rw As Long

    ' check for changes occured on the  "Totals" sheet only
    If Sh.Name <> Sheet1.Name Then Exit Sub

    ' prevent retrigger and reverse the change
    Application.EnableEvents = False

    With Sheet1
        LstRw = .Range("B" & Rows.Count).End(xlUp).Row
        ' compare each cell in D with LastVals to find changes
        For Rw = 3 To LstRw
            ' see if a value changed (allowing for offset
            If LastVals(Rw, 1) <> "Buy" And .Cells(Rw, 4).Value = "Buy" Then

                    ' if it changes to Buy, add C to E...
                    .Cells(Rw, 5).Value = .Cells(Rw, 5).Value + .Cells(Rw, 3).Value

                ElseIf LastVals(Rw, 1) <> "Sell" And .Cells(Rw, 4).Value = "Sell" Then
                    ' if it changes to Sell, add C to F...
                    .Cells(Rw, 6).Value = .Cells(Rw, 6).Value + .Cells(Rw, 3).Value

                Else
                    ' assume no change and do nothi45ng
            End If
        Next Rw

        'save changed values
        LastVals = .Range("D1:D" & LstRw).Value
        Application.EnableEvents = True

    End With

End Sub

(This is slighly changed from the code in my Answer to your recent Question under Record value when change in respective cell. It now looks for changes to "Buy" or to "Sell")

The second attached file is renamed (to include an underscore since your link needed that). If you open that first then the .xlsm file, you can change Trade labels and quantities in the remote file and see them reflected as instantaneous values (in C of the .xlsm file)  and sums (in columns E and F).

Hope this fixes your problem. If so, pllease remember to mark the Answer as Seelected (and next time, make an attempt to solve your own problem first please).