Hi everyone,

Regarding my previous post,

I googled a bit and something called Relationships and Data Model look promising to ease my pain. But I am not familiar with none of these... Well, I am relatively good with excel but very new to Relations and Data Model. I want to keep things simple to understand the consept.

Let say, I have a Raw Materials sheet with only two columns name of the material and cost. Cost is the price that I buy the item.

<tbody>

</tbody>

Now, my Product sheet will include product names and costs, too.. But I will calculate costs according to a third sheet which is more like a support table. This table called "Formula" will include which product is made of which material and amounts.

<tbody>

</tbody>

So the formulas of each Product are listed below:

<tbody>

</tbody>

So I have 3 main questions:

1. Can Relationships handle any of VLOOKUP() or SUMIF() fields. How?

2. I don't want to use Data Model because not everyone has Excel 2016 but if Relations can't... Can I achieve his with Data Model? How?

3. Are all these "Pivot Table"ble? Like, Products are row items. Raw materials are sub-row items. Maybe quantities and costs are on the columns. Grand totals of each product...

What do you think?

Can anybody describe to me step by step any of these? A sample workbook will be appraciated to play around. Just for understanding the concept.

Yes I play Simcity Buildit lateley :D

Thanks a lot folks!!

Regarding my previous post,

I googled a bit and something called Relationships and Data Model look promising to ease my pain. But I am not familiar with none of these... Well, I am relatively good with excel but very new to Relations and Data Model. I want to keep things simple to understand the consept.

Let say, I have a Raw Materials sheet with only two columns name of the material and cost. Cost is the price that I buy the item.

**RawMaterials**A | B | |

1 | Material | Cost |

2 | Iron | 40 |

3 | Wood | 50 |

4 | Plastic | 25 |

<tbody>

</tbody>

Now, my Product sheet will include product names and costs, too.. But I will calculate costs according to a third sheet which is more like a support table. This table called "Formula" will include which product is made of which material and amounts.

**Products**A | B | |

1 | Product | Cost |

2 | Hammer | =SUMIF($A2, Formula!$A:$A, Formula!$D:$D) |

3 | Showel | =SUMIF($A3, Formula!$A:$A, Formula!$D:$D) |

<tbody>

</tbody>

So the formulas of each Product are listed below:

**Formulas**A | B | C | D | |

1 | Product | Material | Qty | Cost |

2 | Hammer | Iron | 2 | =VLOOKUP($B2, RawMaterial!$A:$B, 2, )*$C2 |

3, | Hammer | Wood | 1 | =VLOOKUP($B3, RawMaterial!$A:$B, 2, )*$C3 |

4 | Showel | Iron | 3 | =VLOOKUP($B4, RawMaterial!$A:$B, 2, )*$C4 |

5 | Showel | Wood | 2 | =VLOOKUP($B5, RawMaterial!$A:$B, 2, )*$C5 |

6 | Showel | Plastic | 1 | =VLOOKUP($B6, RawMaterial!$A:$B, 2, )*$C6 |

<tbody>

</tbody>

So I have 3 main questions:

1. Can Relationships handle any of VLOOKUP() or SUMIF() fields. How?

2. I don't want to use Data Model because not everyone has Excel 2016 but if Relations can't... Can I achieve his with Data Model? How?

3. Are all these "Pivot Table"ble? Like, Products are row items. Raw materials are sub-row items. Maybe quantities and costs are on the columns. Grand totals of each product...

What do you think?

Can anybody describe to me step by step any of these? A sample workbook will be appraciated to play around. Just for understanding the concept.

Yes I play Simcity Buildit lateley :D

Thanks a lot folks!!

Last edited: