Auto-adjust stock levels based on drop-down input

Hello,

I am attempting to create a shipping request form for a department in my company to use. At this point, I have it set up so that the user can fill out relevant fields, select what they want to ship based on a drop down, and have it email and save itself using a unique reference. This all works exactly as it should.

Please bear with me while I attempt to explain my question.

There are two worksheets in this file. The first sheet of this spreadsheet contains the ship request. The second worksheet contains a list of part numbers in column A, a detailed description of these part numbers in column B, and the quantity of that item that is in stock in column F. When the user selects their part number from the drop down in the ship request, the description is pulled from the part number sheet column B using VLOOKUP and auto-populated into the ship request. After that, the user enters the quantity of the item that they want to ship. Here is where my problem lies:

What I would like is when the user selects the part from the drop down, and then enters the quantity to ship, I need that entered quantity to be subtracted from the quantity in stock (column F on the part number sheet), based off the value selected in the drop down. I've been trying all sorts of things, but my difficulty lies in having the drop down value dictate which cell the subtraction takes place in. I understand that this must be done in VBA, as I cannot have a cell contain both a value and a formula. How do I have this form recognize that the quantity needs to be subtracted from the appropriate row in Sheet 2, based on the value selected from the drop-down?

Hopefully this makes sense! Can any skilled Excel users out there help me out?

This is a link to a Dropbox example of my question. This is obviously quite simplified, but illustrates exactly what I need to have take place:

https://www.dropbox.com/s/rajpzh2ur8...mple.xlsx?dl=0

Thank you in advance for any help you can provide!

1.jpg
2.jpg

ppgab,

This is beautiful - thank you very much for the extra effort!!

I will attempt to integrate this into my file and see if I can't tweak it to get it to work for what I need. I'm backlogged with some other stuff at the moment, but I'm sure you'll hear from me soon. Again, thank you very much!!