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
 

Some videos you may like

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
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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?

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
Top