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

adnakarivach

New Member
Joined
Nov 1, 2004
Messages
23
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!!!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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:
Code:
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

New Member
Joined
Nov 1, 2004
Messages
23
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.... :confused:
thanks again for any ideas.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
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

New Member
Joined
Nov 1, 2004
Messages
23

ADVERTISEMENT

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" :cry: 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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Umm, it does that. It doesn't overwrite any existing answer.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,271
Messages
5,577,124
Members
412,769
Latest member
VK12345
Top