Using Relationships to calculate cost

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
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 by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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!)
 
Upvote 0
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 by a moderator:
Upvote 0
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.
 
Upvote 0
Is there anyway to get rid of SUMIF() also?
 
Last edited by a moderator:
Upvote 0
for the VLOOKUP:
Code:
=VLOOKUP([@Material],t_MatCost[#All],2,FALSE)*[@Qty]

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

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top