Generate values in a table based on dropdown selection (raw materials in cable production)

In the attached workbook you can see I have 6 types of AC6 cable and a list of raw materials. The different cable types use different quantities of each raw material and some don't use certain raw materials at all. I want to be able to let the user choose the cable type from a drop down list and it automatically generates the blank final column based on their selection. I tried data validation and combobox (form control) but neither one seems cable of doing it. Also I need the blank column to ideally be on a front page by itself as the main user interaction page and the values for each cable type on a different worksheet in the background. How can I go about this?