Calculate committed inventory total based on BOMs and a schedule

Hi there,

Looking for some help calculating committed inventory based on a schedule and number of BOMs (bill of materials). So basically I have a workbook with the first worksheet being a schedule of widgets to build with part numbers, a second sheet with all the parts used in building the different widgets, and then a number of BOMs (each on separate sheet with unique part numbers) with parts required for each different widget. Each widget has a unique list of parts and quantities used but some of the parts are used across multiple widgets. A widget may show up more than once in the schedule or not at all.

What I'm trying to do is go through each widget in the schedule then add the parts required to build each into the 2nd sheet so I can have a running total of committed inventory. I've attached a sample workbook if my description is tough to follow.

Any help would be greatly appreciated. Cheers!