Using Relationships to calculate cost

[Legacy 143009]

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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Material[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Iron[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Wood[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Plastic[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hammer[/TD]
[TD]=SUMIF($A2, Formula!$A:$A, Formula!$D:$D)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Showel[/TD]
[TD]=SUMIF($A3, Formula!$A:$A, Formula!$D:$D)[/TD]
[/TR]
</tbody>[/TABLE]

So the formulas of each Product are listed below:

Formulas[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Material[/TD]
[TD]Qty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hammer[/TD]
[TD]Iron[/TD]
[TD]2[/TD]
[TD]=VLOOKUP($B2, RawMaterial!$A:$B, 2, )*$C2[/TD]
[/TR]
[TR]
[TD]3,[/TD]
[TD]Hammer[/TD]
[TD]Wood[/TD]
[TD]1[/TD]
[TD]=VLOOKUP($B3, RawMaterial!$A:$B, 2, )*$C3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Showel[/TD]
[TD]Iron[/TD]
[TD]3[/TD]
[TD]=VLOOKUP($B4, RawMaterial!$A:$B, 2, )*$C4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Showel[/TD]
[TD]Wood[/TD]
[TD]2[/TD]
[TD]=VLOOKUP($B5, RawMaterial!$A:$B, 2, )*$C5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Showel[/TD]
[TD]Plastic[/TD]
[TD]1[/TD]
[TD]=VLOOKUP($B6, RawMaterial!$A:$B, 2, )*$C6[/TD]
[/TR]
</tbody>[/TABLE]


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!!

 

[SpillerBD]

You don't need Excel 2016 to use Relationships or Data Model. The Data Model is available to everyone, has been! What you get with 2016 is Get&Transform and the possibility of PowerPivot (Now included for O365 subscribers for 2018!)

 

[Legacy 143009]

You don't need Excel 2016 to use Relationships or Data Model. The Data Model is available to everyone, has been! What you get with 2016 is Get&Transform and the possibility of PowerPivot (Now included for O365 subscribers for 2018!)

Oh, well then. That's great. So, is there anyway to achieve this without PowerPivot?

 

[SpillerBD]

Actually. On your "Raw Materials" range, select a cell then press CTRL+T to make it a Table.
On you formulas range, clear out your current formulas in column D. Then make it a table with the CTRL+T keystroke.
Now, rebuild the VLOOKUP * Quantity formula. That will give you the expanding range of Tables.
With that done, select any 1 cell in the Tale, got the Insert ribbon, and select Pivot Table. "Product" for row and "Cost" for your Sigma (Sum of) value.

 

[Legacy 143009]

Ok, thank you very much. Nearly got it! But please be pricise with the following two:

Now, rebuild the VLOOKUP * Quantity formula.

How my formula should exactly look like?

With that done, select any 1 cell in the Tale,

Which table to click?

 

[Legacy 143009]

Is there anyway to get rid of SUMIF() also?

 

[SpillerBD]

for the VLOOKUP:

=VLOOKUP([@Material],t_MatCost[#All],2,FALSE)*[@Qty]

The SUMIF(s) will be rid of by using the Pivot Table.