Update total "In Stock" on separate workbook automatically

Hi,
I am hoping someone can help me with this - I have two workbooks, one is the inventory master, with product name, id, qty, cost, markup, etc. (attached) and the second is the order workbook, which has drop down menu for products, qty, and cost and sell price totals (attached) - and all info is transferred between workbooks using VLOOKUP.

I would like to be able to have the in stock qty in the inventory master workbook automatically deduct the qty that is being ordered from the order workbook.

I hope that this makes some sense - I am sorry if I am not being clear. But please, any advice is helpful.

Cheers,
Nic.

One of the ways to do this is - have a In Stock column with the current values (in the Master file), maybe you can have it hidden. Then have another column with this formula -

Please Login or Register  to view this content.

But, this formula will only work if the test file has the Product Name column on the left and the Qty on the right. Currently, its the other way around and so the formula doesnt yield any answer. So it will be good if you can change the layout of the test file a little to accommodate this.

Thank you so much for that!! perfect... very, very much appreciated!

Cheers,
Nic.