If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Non reciprocal formula?
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. |
#2
|
|||
|
|||
Non reciprocal formula?
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 "Tuk" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|