# + and - (plus and minus) in Excel list

#### David77

##### Board Regular
Hello everybody,

I am currently currently managing a stock overview in Excel (manually), where I have a set quantity. I want to create two columns next to my quantity column where I can enter numbers that should be substracted and added to the quantity, so it looks like this:

Now obviously I can just set the quantity number as (for I1 for example): =50-J1+K1, which would give me my current quantity. However, for future endeavours when the stock continues to decrease or increase, I would have to enter new numbers in J1 and K1 and thus my formula would break down as it would forget about the prior entries.

Is there any fancy way in which I can fix this? So I can simply just type in "5" in J1, whereafter it subtracts 5 from I1 = 45 and the number then disappears from J1.

I would greatly appreciate any assistance I can get in this matter! Thank you very much for your time everybody

Best regards,
David

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### FormR

##### MrExcel MVP
Hi, you could try this code.

To use right, click the sheet tab where you want this to happen and choose "view code" and paste the below code into the window that opens up and then save your workbook as a macro enabled workbook (*.xlsm).

VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("J:K"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
With Cells(c.Row, "I")
If IsNumeric(.Value) Then
.Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)
c.ClearContents
End If
End With
Next c
Application.EnableEvents = True
End If
End Sub``````

#### David77

##### Board Regular
Hi, you could try this code.

To use right, click the sheet tab where you want this to happen and choose "view code" and paste the below code into the window that opens up and then save your workbook as a macro enabled workbook (*.xlsm).

VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("J:K"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
With Cells(c.Row, "I")
If IsNumeric(.Value) Then
.Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)
c.ClearContents
End If
End With
Next c
Application.EnableEvents = True
End If
End Sub``````

It works!!

Amazing! Thank you so much!!!

Please have a wonderful day, you've ensured that I will now

Best regards,
David

Replies
2
Views
146
Replies
0
Views
125
Replies
12
Views
1K
Replies
14
Views
348
Replies
1
Views
207

1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back