Recipe tracker

[jcarroll012]

To make the question easier I am posting it as a recipe tracker

One table has the recipe

[TABLE="width: 500"]
<tbody>[TR]
[TD]Recipe #[/TD]
[TD]Recipe[/TD]
[TD]Bread[/TD]
[TD]PB[/TD]
[TD]Jelly[/TD]
[TD]Broth[/TD]
[TD]Chicken[/TD]
[TD]Noodles[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]PBJ[/TD]
[TD]50%[/TD]
[TD]25%[/TD]
[TD]25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Chicken Soup[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]10%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Chicken Sandwich[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Peanut Butter Soup[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD][/TD]
[TD]90%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Second table has weekly orders and needs the total amount of components for the week to track ordering

[TABLE="width: 500"]
<tbody>[TR]
[TD]Recipe #[/TD]
[TD]Recipe[/TD]
[TD]Week of 7/3/2016 (#made)[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]PBJ[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Chicken Soup[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Chicken Sandwich[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]103 [/TD]
[TD]Peanut Butter Soup[/TD]
[TD]51[/TD]
[/TR]
</tbody>[/TABLE]

Output

[TABLE="width: 500"]
<tbody>[TR]
[TD]Ingredients[/TD]
[TD]Week of 7/3/2016 (Used)[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]PB[/TD]
[TD]15.1[/TD]
[/TR]
[TR]
[TD]Jelly[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Broth[/TD]
[TD]120.9[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Noodles[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

This goes on for weeks and weeks laid out as above. I've stared at it for a while now and tried a lot of things, but I think I'm staring too hard.

 

[Marcílio_Lobão]

jcarroll012, Good afternoon.

It looks like be a food's production planning.

What's your doubt?
What kind of help you need exactly?

 

[jcarroll012]

I'm trying to replicate the output (by week) for the ingredients used for the various recipes during that same week.

The output I posted is me doing the math manually.....I haven't figured out how to structure a formula to replicate it. Everything I come up with involves multiple columns per week and it expands the structure of the other tables significantly.

It is indeed production planning.

 

[jcarroll012]

Obviously this can be done manually in its simplest form, but I am trying to scale this to 11 ingredients and 250+ recipes. I need the "used" ingredients to build off of what would be used by making x number of the recipes per week.

 

[jcarroll012]

I haven't figured if this is too easy or too hard.

 

[jcarroll012]

Or is there anything I can do to the data to make the calculation easier?