Production Planning Model

[Ebruin]

I know after reading this some may say "Fix your ERP system" to which I agree, but since that's not viable I'm working on a temporary fix to production planning issues.

In a nutshell we have a number of products with associated routers and travellers. It isn't to hard to get production planning to export the time phased data by labor catagory for an individual part which may look like single unit model on the attached spreadsheet.

What I'm trying to do and failing so far is when data is entered into the Build plan row, the range of cells B7:Z10 are automatically updated with the overall manpower estimate to support that build schedule. I filled in rows 13 through 17 to show how the data would sums up based on the entries in J5:L5.

Ideally, the formula should be robust enough with knowing the total build time (calculated in "Single Unit Model" C2), that it can calulate the hours for that labor category for that month.

I've tried combinations of SUMPRODUCT. My worry is that there is a recursive element I' may not be accounting for that may preclude formula use.
I'm trying to do this without writing VBA code, but can if I must.

Thanks in advance for any insights, suggestions, or solutions.

Ebruin

Model Template.xlsx

[Ebruin]

Any ideas anyone?