Find last cell in column and subtract.

Justin

Your formula was in column D (not C as you said) but you say that there is  "other code within the workbook" so I'll suggest a VBA answer. This means you won't need a formula in column D (I've deleted them in the attached file).

The code below is "behind" Sheet1 in the attached , revised file. It uses the Worksheet_Change event which  is triggered whenever an entry is made on that sheet (or the same value is confirmed by "Enter"). Target is what VBA recognises as the cell(s) changed incidentally.

I've commented the code below so you can (hopefully) follow what's going on.

Private Sub Worksheet_Change(ByVal Target As Range)
  
'do nothing if column C wasn't changed or more than one cell was changed
If Intersect(Target, Columns(3)) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
'do nothing if  if a number wasn't entered
If IsNumeric(Target.Value) = False Then Exit Sub

' disable events so thghis macro isn't re-triggered if it chnages values
Application.EnableEvents = False
    'loop back through rows
    For n = Target.Row - 1 To 2 Step -1
    ' see if there's a value in each previous row
        If Cells(n, 3).Value <> "" And IsNumeric(Cells(n, 3)) Then
            ' if so, make D = previous last less new number
            Target.Offset(0, 1).Value = Target.Value - Cells(n, 3).Value
            ' ### optional- clear all other values in D
            'Range("D2:D" & Target.Row - 1).Value = ""
            
            ' stop checking
            Exit For
        End If
    Next n
' re-enable events
Application.EnableEvents = True

End Sub

To test it, put  a value in C36 say- it will put a value in D36.

It will add a value anytime you change a value in C but if you want it to clear the previous values in D, uncomment the line in bold above.

Note that (after a couple of checks to ensure just a single value was entered) there's a line Application.EnableEvents = False (so that the macro doesn't run again as it any makes changes).

You'll see that it loops backwards through column C (from the changed row) to work out the last non-blank value and then adds a calculated value in column D and stops looping back. 

If however you really need a formula, paste this into cell D3:

=IF(ISBLANK(C3),"",C3-LOOKUP(2,1/(C$2:C2<>""),C$2:C2))

and copy it down column D. Unless there's a blank in column D, it will find the last non-blank value and subtract it from what's in column C

Hope this helps.