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.

Hopefully this makes sense! Can any skilled Excel users out there help me out?
1.jpg
2.jpg

Please upload an Excel file showing expected results.

You will need to use VBA for this as a cell cannot contain both a value and a formula.

OK - I will look into that option. Thank you very much.