Hi, hopefully this all makes sense. All I am trying to do is to show how much various products cost to produce based on where the production is and the ingredients used.
This is how I have my recipes set out, with a 1 showing if the ingredient is used in that particular product -
Excel 2007
<tbody>
</tbody>
Here I show the costs of each ingredient. Each ingredient will have a cost for that country however there may be exceptions where town or the county has a different cost compared to the country where it is.
In essence if there is a town cost then that is used, if not then a county cost and otherwise the country cost.
Excel 2007
<tbody>
</tbody>
This is where I calculate the costs by looking at each individual ingredient for that product / town combination and seeing whether special town or county costs are used, if not then default to the country one.
The formula for C31 is below and this is filled across through to M32
Now this code works fine but is very clunky and means that I have to look up each individual ingredient. Is there a more efficient way to complete this calculation either with formula or VBA as the scalability of it is causing headaches!
Thanks in advance.
Excel 2007
<tbody>
</tbody>
This is how I have my recipes set out, with a 1 showing if the ingredient is used in that particular product -
Excel 2007
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | Recipe | ||||||||||
2 | Ingredient 1 | Ingredient 2 | Ingredient 3 | Ingredient 4 | Ingredient 5 | Ingredient 6 | Ingredient 7 | Ingredient 8 | Ingredient 9 | Ingredient 10 | |
3 | Product 1 | 1 | 1 | 1 | |||||||
4 | Product 2 | 1 | 1 | ||||||||
5 | Product 3 | 1 | 1 |
<tbody>
</tbody>
Sheet1
Here I show the costs of each ingredient. Each ingredient will have a cost for that country however there may be exceptions where town or the county has a different cost compared to the country where it is.
In essence if there is a town cost then that is used, if not then a county cost and otherwise the country cost.
Excel 2007
A | B | C | D | E | F | G | H | I | J | K | L | M | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
8 | Variation | ||||||||||||
9 | Country | County | Town | Ingredient 1 | Ingredient 2 | Ingredient 3 | Ingredient 4 | Ingredient 5 | Ingredient 6 | Ingredient 7 | Ingredient 8 | Ingredient 9 | Ingredient 10 |
10 | UK | 5 | 10 | 15 | 3 | 6 | 9 | 2 | 4 | 6 | 10 | ||
11 | France | 3 | 2 | 4 | 5 | 9 | 9 | 7 | 8 | 6 | 31 | ||
12 | UK | Lancs | |||||||||||
13 | UK | Lancs | Town A | ||||||||||
14 | UK | Lancs | Town B | ||||||||||
15 | UK | Lancs | Town C | ||||||||||
16 | UK | Yorks | 9 | 5 | |||||||||
17 | UK | Yorks | Town D | ||||||||||
18 | UK | Yorks | Town E | 2 | 3 | 4 | |||||||
19 | UK | Yorks | Town F | ||||||||||
20 | France | Vendee | 6 | 8 | 9 | 9 | |||||||
21 | France | Vendee | Town G | ||||||||||
22 | France | Vendee | Town H | ||||||||||
23 | France | Vendee | Town I | ||||||||||
24 | France | Brittany | |||||||||||
25 | France | Brittany | Town J | ||||||||||
26 | France | Brittany | Town K |
<tbody>
</tbody>
Sheet1
This is where I calculate the costs by looking at each individual ingredient for that product / town combination and seeing whether special town or county costs are used, if not then default to the country one.
The formula for C31 is below and this is filled across through to M32
Code:
=(IF(INDEX(D$10:D$26,MATCH($B31,$C$10:$C$26,0))<>0,INDEX(D$10:D$26,MATCH($B31,$C$10:$C$26,0)),IF(INDEX(D$10:D$26,MATCH(INDEX($B$10:$B$26,MATCH($B31,$C$10:$C$26,0)),$B$10:$B$26,0))<>0,INDEX(D$10:D$26,MATCH(INDEX($B$10:$B$26,MATCH($B31,$C$10:$C$26,0)),$B$10:$B$26,0)),INDEX(D$10:D$26,MATCH(INDEX($A$10:$A$26,MATCH($B31,$C$10:$C$26,0)),$A$10:$A$26,0)))))*INDEX(B$3:B$5,MATCH($A31,$A$3:$A$5,0))
Now this code works fine but is very clunky and means that I have to look up each individual ingredient. Is there a more efficient way to complete this calculation either with formula or VBA as the scalability of it is causing headaches!
Thanks in advance.
Excel 2007
A | B | C | D | E | F | G | H | I | J | K | L | M | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
28 | Calculations | ||||||||||||
29 | |||||||||||||
30 | Ingredient 1 | Ingredient 2 | Ingredient 3 | Ingredient 4 | Ingredient 5 | Ingredient 6 | Ingredient 7 | Ingredient 8 | Ingredient 9 | Ingredient 10 | TOTAL | ||
31 | Product 1 | Town D | 9 | 10 | 0 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 24 |
32 | Product 2 | Town G | 3 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 |
<tbody>
</tbody>
Sheet1