Forum Discussion
Help with a changing qty on hand formula
I'm having a hard time getting a formula to work. I have 3 columns, one is Qty on Hand, the next they would like to add to the qty on hand and the third column to subtract from the qty on hand. I need the qty on hand to always maintain the updated quantity. so if I have 40 and add 4 it stays 44. when I subtract 6 it needs to stay 38. The next time 10 would be added the qty would change to 48, but not subtract the 4. Does this make sense? Any help would be greatly appreciated.
14 Replies
I'm having issues at the moment trying to get this to open. Is there anyway you can type out the info? I'm sorry to ask, our system just keeps throwing security alert and I can't even get to allow macros to watch. my apologies
This is what the sheet looks like:
And this is the code behind the worksheet:
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False If Not Intersect(Range("B2"), Target) Is Nothing Then Range("A2").Value = Range("A2").Value + Val(Range("B2").Value) End If If Not Intersect(Range("C2"), Target) Is Nothing Then Range("A2").Value = Range("A2").Value - Val(Range("C2").Value) End If Application.EnableEvents = True Application.ScreenUpdating = True End Subthank you so very much, I will give this a try!!! I sincerely appreciate all your help!!!
thank you so much. I can not open it at this time, but will look at it later today. I appreciate your help!
I thought I had replied, I'm sorry. That is fine we will just work on the Windows PC for now. I can figure out a solution for the iPad later.
This could easily lead to mistakes without a way to correct them, since you'd have no history of changes.
I'd use a separate row for each change.
I completely understand what you are saying, however, they just want the 3 without adding more rows.
That would require VBA code, so it'll work on the desktop version of Excel for Windows and Mac only.
Users will have to allow macros, and entering a number in the Add or Subtract cell will disable Undo.
See the attached demo workbook.
Right-click the sheet tab and select View Code to inspect the VBA code.
