How to keep the result of a formula before it is changed?

[adnakarivach]

Please help with this:
There is a formula in cell c3 that states = a1+b1.
now a1 and b1 will vary 50 different times, but I need to keep the result from each "a1" and "b1" transaction. For example:
1+2 = 3, the "3" needs to stay in cell "d1" because "a1" and "b1" will change to say 2+3 = 5, then "5" needs to be on cell "d2".
but everytime I try, I end up with the same numbers on all "D" cells.
any advise??
Thanks!!!

 

[tactps]

You can not do this via formula, you need VBA:

Press Alt-F11 to bring up VBA
In the left pane, double-click the sheet you want to attach this to.
In the right pane, paste this in:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 3 And Target.Row = 1 Then [d65536].End(xlUp).Offset(1, 0) = [a1] + [b1]
End Sub

Is that what you're after?

 

[adnakarivach]

Thanks for the prompt response.
If I am not mistaken, this code will "fill" in the next cell down on the target column; how can I give it an specific cell for each transaction?
lets say transaction #1 was a1+b1 = 45, then I would "click" on the code and a1 and b1 will be cleared, BUT the "45" will stay in say D1,
then a1+b1 = 5 then I clik on the code and the "5" will go to D2 and a1 and b1 will be cleared....and so on. I know the code to do the clearing, but lack the code to make the result stay UNCHANGED in a cell when the variables change....
thanks again for any ideas.

 

[Scott Huish]

Going to add a bit to tactps's code, you won't have to select the macro each time and it will wait until there is a numeric value in A1 and B1 before proceeding, it will then add them up and put them in the next available cell in Column D, blank A1 and B1 and select cell A1:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 3 And Target.Row = 1 Then
    If IsNumeric([A1]) And IsNumeric([B1]) And [A1] <> "" And [B1] <> "" Then
        Application.EnableEvents = False
            [d65536].End(xlUp).Offset(1, 0) = [A1] + [B1]
            Range("A1:B1").ClearContents
            [A1].Select
        Application.EnableEvents = True
    End If
End If
End Sub

 

[adnakarivach]

Thank you greaty for the kind help. It certaintly is moving me in the right direccion; However, I woud like to pose the probrem in this perspective:
if I input a "5" in cell a1, and a "3" in cell b1, the total THEN would be "8" on cell d1, NOW someone comes and CHANGES the value of a1....I need cell d1 TO MAINTAIN the original "8" EVEN if the original values changed ... I guess kind of a "single use formula".
Thanks again guys and have a Happy New Year!

 

[Scott Huish]

Umm, it does that. It doesn't overwrite any existing answer.

 

[adnakarivach]

I was not doing it properly....
Thanks a million!