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 -
=C2-SUMIF([test.xlsx]Sheet1!$K$5:$L$11,A2,[test.xlsx]Sheet1!$L$5:$L$11)
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.
Bookmarks