Subtract Values from cells in one sheet from another sheet

Title sounds simple but to me this problem is a tuffy!

I have an inventory database that my business is very dependent on. I have built it over the years and I'm continuously coming up with ways to make it more functional and accurate. The 2 images attached only show a small sample of the database. The first image is our "Workflow" for the week and the other is my "Main Inventory". The "Workflow" sheet has 228 rows and on our busy weeks most are used for each of our skus that need replenished. The Main Inventory sheet has over 2,000 part numbers.

My objective is that for each sku line on the Workflow I want to be able to tag it with it's status on a daily basis. You see the Label Created in the Status column and that will simply be a drop-down list. For the "Shipped" option then this means it was picked up and has left our warehouse. Once it goes to "Shipped" then I want the inventory deducted from the Main Inventory and may have up to 4 part number quantities to update. So, I need to take the quantity for all 4 Part#'s that shipped out of inventory (the number to be deducted is in Column I) which for the top sku equals "50". The "Qty" column is our "in-house" inventory for each Part#. And at the same time I want the "Status" column changed to "Shipped" and the "Status Date" updated.

As I mentioned there maybe up to 228 sku's to ship each week which means 228 rows which would also mean 228 "Shipped" command buttons. If I have to use a command button for each row then I have massive amounts of redundant copy, pasting and changing cell references in VBA code. Is there a way to avoid this while being able to still update line by line as sku's ship?

FYI, all the sku information on the Workflow is pulled, using vlookup, from another sheet (not included) and that sheet pulls the data from the Main Inventory sheet. That's why the Workflow sheet has to update (subtract) quantity of each Part# that shipped for that sku.

Below is column references for the images:

The Workflow columns start with "B" which has the sequential row numbers of 1, 2, 3...... And the "Part#1" is in column "L" and it's Qty is column "M". The "Status" column is "T".

The Main Inventory columns start with the part number in Column "B". The "In House Qty" is column "E".

Andy,

Appreciate the description, but will be a lot easier to help if you can provide copies of the various worksheets involved? (Just a few rows of "dummy" data on each will be sufficient, as it's the structure that's important, not the actual values?)

Ochimus

Hi Ochimus,

I appreciate the response but I got it working mostly to my liking. I did run into another problem but I'll have to create another post for that. Sorry I didn't respond back sooner.

Andy