L
Legacy 143009
Guest
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.
RawMaterials
<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
<tbody>
</tbody>
So the formulas of each Product are listed below:
Formulas
<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 by a moderator: