Each part requires a different amount of material so I want somebody to select a product number from a drop down menu that I make ...
The Dropdown list can be made via DATA VALIDATION

...and then it will fill in the cell next to it with the material needed for one unit. How do I do this????
Filling in the cell next to it can be done via INDEX, VLOOKUP, HLOOKUP or LOOKUP