+ and - (plus and minus) in Excel list

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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:

1596525471501.png


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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,139
Members
453,021
Latest member
Justyna P

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?

Disable AdBlock

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
Back
Top