Use previous value in formula

[mayerr]

I have searched everywhere and cannot find a solution. I'm not even sure it is possible, but this is what I'm trying to do. In cell A1 I will enter data. I want to subtract that value from cell B1 and put the result into C1. That by no means is my issue. Here is what I want. I want to use the value that I typed in A1 as my new value of B1, and enter a new value into A1. The result needs to stay in the same cell C1. Maybe this will help? This is what I currently have: A1 Data, B1 Data, C1 =mod(b1,100)-mod(a1,100). I need the value from A1 to be placed into B1 and then enter a new value into A1, with the result remaining in C1. Thank you to anyone who can tell me how, or if it is possible.

Ray

 

[mikerickson]

I'm having trouble picturing what you want

Let's start with an initial state (A1, B1, C1)

10, 2, 8
C1 = A1-B1 so all is good.

At that point, the user enters 17 into A1. You want the A1 value (10) to move to B1.

17, 10, ??

What I don't understand is the "The result needs to stay the same in C1". Does that mean that you want 8 to remain in C1 or do you want C1 to remain the difference between B1 and A1?

 

[mayerr]

I'm having trouble picturing what you want

Let's start with an initial state (A1, B1, C1)

10, 2, 8
C1 = A1-B1 so all is good.

At that point, the user enters 17 into A1. You want the A1 value (10) to move to B1.

17, 10, ??

What I don't understand is the "The result needs to stay the same in C1". Does that mean that you want 8 to remain in C1 or do you want C1 to remain the difference between B1 and A1?

I did not explain that very well. I'm sorry! I want the value in C1 to be the new difference, or remain the difference between B1 and A1. So in your example I would like 7 to be displayed in C1.

 

[mayerr]

I think I have this solved.... I'm still verifying. Had to use a micro and run a VBA project.

Will wait till tomorrow before I close this.

 

[mayerr]

I think I will call this solved. If anyone is interested search for: How To Remember Or Save Previous Cell Value Of A Changed Cell In Excel?

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-formulas-and-functions/1246558-use-previous-value-in-formula.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[mikerickson]

This is my notion of how to do the OP. Note that the formula =A1-B1 in cell C1 is not a part of this process and can be controlled by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "$A$1" Then
            On Error GoTo MakeValidation
            .Offset(0, 1).Value = .Validation.InputTitle
            Application.EnableEvents = False
            .Validation.InputTitle = .Value
            Application.EnableEvents = True
        End If
    End With
Exit Sub
MakeValidation:
    With Target.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=true"
    End With
    Resume
End Sub