Increment cell value based on change in value of another cell [Closed]

Report
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
Hello,

Say I have a value of 100 in cell B3 and I need this value to increase/decrease based on the change in value of A2. How do I do that? For example, if the value of A2 is 50 and it increases to 70, I need the 100 in B3 to increase by the change, that is, by 20.

Can someone please help me with this as soon as possible? I will be extremely grateful.

Many thanks,
Sarah

1 reply

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Hi Sarah,

The closest I can come to your solution is this:
Dim x As Integer
Private Sub Worksheet_Activate()
x = Range("A2").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
Dim y As Integer
y = Range("A2").Value - x
Range("B3").Value = Range("B3").Value + y
End Sub


Paste the code under the sheet it is for. This is the way it works:
Make sure you have at least 2 sheets.
Go to sheet 1 and enter 50 in A2 and 100 in B3.
Now go to sheet 2 and then back to sheet 1. Excel now has the value 50 in it's memory.
Now change 50 into 70 in A2 and Excel will subtract the value in it's memory (50) from the new value and add the difference to B3.
To change Excels memory from 50 to 70, you will need to go to sheet 2 and then back to sheet 1.


Otherwise you will have to think of another way of doing this, like using another cell for the increment and then add that cell to both A2 and B3.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.