calculations through drop down list in excel

Hi everyone,

I need help with calculations based on the values chosen by the user through a drop down list. (I have attached the sheet for reference)

Parts are manufactured from raw materials that are given by 6 vendors (a-e). So, after writing the quantity, the user will choose the vendor through which the raw material was taken from and calculate the remaining stock.
for example, if a product (300 units) is manufactured by the raw material of vendor A (say 700 units) , I want the stock to be 400. Again, if the 200 units are manufactured from A (the type of product does not matter, the stock should be 400-200 and not 700-200. Same for all other vendors.
Also, is there a method to add new vendors automatically to the list and the formula?

Any help/lead will be appreciated.
I have already figured out to do the calculations for the first part; I need help with the second part (if products are manufactured from the same vendor)

Thank You guys!!

Thank You so much
It worked!!!