Non reciprocal formula?

  • Thread starter Thread starter Tuk
  • Start date Start date
T

Tuk

Hi, can anyone help with this? I need formulae which demonstrate a flow in
one direction. I'll put it a simply as I can.

I want to be able to add manually, a value to cell 'A'

When I manually subtract value from cell 'A' a formula adds that value to
cell 'B'

That much I could do but it is conditional upon the following.

For this to work I then need to be able to manually subtract from values in
cell 'B' without the formula in the first stage returning that value to cell
'A'. It has gone elsewhere, not back to where it came from.

Can Excel do this?

Help much appreciated.
 
You say:
I want to be able to add manually, a value to cell 'A'".
What do you mean by "add"? Are you entering a value or are you increasing
the value by entering a higher value?
You say:
"When I manually subtract value from cell 'A' a formula adds that value to
cell 'B'".
By "subtract", do you mean you enter a value that is less than the previous
value?
You say:
"For this to work I then need to be able to manually subtract from values in
cell 'B' without the formula in the first stage returning that value to cell
'A'. It has gone elsewhere, not back to where it came from."
A formula, in a cell, can not produce a change to the contents of another
cell.
You will need VBA for this.
The following macro placed in the sheet module will do that. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Double
Dim NewValue As Double
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.ScreenUpdating = False
NewValue = Target
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
If NewValue < OldValue Then _
Target.Offset(, 1) = Target.Offset(, 1) + OldValue - NewValue
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
 
Back
Top