Using Relationships to calculate cost

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
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
AB
1MaterialCost
2Iron40
3Wood50
4Plastic25

<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
AB
1ProductCost
2Hammer=SUMIF($A2, Formula!$A:$A, Formula!$D:$D)
3Showel=SUMIF($A3, Formula!$A:$A, Formula!$D:$D)

<tbody>
</tbody>

So the formulas of each Product are listed below:

Formulas
ABCD
1ProductMaterialQtyCost
2HammerIron2=VLOOKUP($B2, RawMaterial!$A:$B, 2, )*$C2
3,HammerWood1=VLOOKUP($B3, RawMaterial!$A:$B, 2, )*$C3
4ShowelIron3=VLOOKUP($B4, RawMaterial!$A:$B, 2, )*$C4
5ShowelWood2=VLOOKUP($B5, RawMaterial!$A:$B, 2, )*$C5
6ShowelPlastic1=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:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
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!)
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
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?
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
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.
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670

ADVERTISEMENT

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?
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
for the VLOOKUP:
Code:
=VLOOKUP([@Material],t_MatCost[#All],2,FALSE)*[@Qty]

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

Watch MrExcel Video

Forum statistics

Threads
1,127,082
Messages
5,622,581
Members
415,909
Latest member
vbaBeginner94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top