Hi Everyone,
I'm looking for some help to simplify the following:
1) I have 1 sheet with a large table A1:D4 showing sales volume (Mt) per productcode (column A) and per months of June, July and August (row 1)
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]ProductCode1[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]ProductCode2[/TD]
[TD]150[/TD]
[TD]80[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]ProductCode3[/TD]
[TD]200[/TD]
[TD]90[/TD]
[TD]350[/TD]
[/TR]
</tbody>[/TABLE]
2) I have a 2nd sheet with a large table A1:E4 showing the % of each ingredients (row 1) that the productcodes (column A) are composed.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Ingredient1[/TD]
[TD]Ingredient2[/TD]
[TD]Ingredient3[/TD]
[TD]Ingredient4[/TD]
[/TR]
[TR]
[TD]ProductCode1[/TD]
[TD]20%
[/TD]
[TD]5%[/TD]
[TD]0%[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]ProductCode2[/TD]
[TD]80%[/TD]
[TD]20%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]ProductCode3[/TD]
[TD]10%[/TD]
[TD]20%[/TD]
[TD]30%[/TD]
[TD]40%[/TD]
[/TR]
</tbody>[/TABLE]
Goal: I'm looking to summarize in 1 (total) cell for each month, the volume (Mt) of the ingredients needed.
So far I managed only to work out ingredient per ingredient, month per month by pulling down -and right- something like the following formula.
Unfortunately this takes up more space than the original tables themselves.
INDEX(Sheet2!B2:E4;MATCH("ProductCode1";Sheet2!A2:A4;0);MATCH("Ingredient1";Sheet2!B1:E1;0))*INDEX(Sheet1!B2:D4;MATCH("June";Sheet1!B1:D1;0))
Would anyone have a better solution, that would allow me to summarize the ingredient volume (Mt) in 1 cell per month?
Thanks in advance for anyone willing to help out!
I'm looking for some help to simplify the following:
1) I have 1 sheet with a large table A1:D4 showing sales volume (Mt) per productcode (column A) and per months of June, July and August (row 1)
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]ProductCode1[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]ProductCode2[/TD]
[TD]150[/TD]
[TD]80[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]ProductCode3[/TD]
[TD]200[/TD]
[TD]90[/TD]
[TD]350[/TD]
[/TR]
</tbody>[/TABLE]
2) I have a 2nd sheet with a large table A1:E4 showing the % of each ingredients (row 1) that the productcodes (column A) are composed.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Ingredient1[/TD]
[TD]Ingredient2[/TD]
[TD]Ingredient3[/TD]
[TD]Ingredient4[/TD]
[/TR]
[TR]
[TD]ProductCode1[/TD]
[TD]20%
[/TD]
[TD]5%[/TD]
[TD]0%[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]ProductCode2[/TD]
[TD]80%[/TD]
[TD]20%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]ProductCode3[/TD]
[TD]10%[/TD]
[TD]20%[/TD]
[TD]30%[/TD]
[TD]40%[/TD]
[/TR]
</tbody>[/TABLE]
Goal: I'm looking to summarize in 1 (total) cell for each month, the volume (Mt) of the ingredients needed.
So far I managed only to work out ingredient per ingredient, month per month by pulling down -and right- something like the following formula.
Unfortunately this takes up more space than the original tables themselves.
INDEX(Sheet2!B2:E4;MATCH("ProductCode1";Sheet2!A2:A4;0);MATCH("Ingredient1";Sheet2!B1:E1;0))*INDEX(Sheet1!B2:D4;MATCH("June";Sheet1!B1:D1;0))
Would anyone have a better solution, that would allow me to summarize the ingredient volume (Mt) in 1 cell per month?
Thanks in advance for anyone willing to help out!