# Using Relationships to calculate cost

#### Flashbond

##### Well-known Member
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
 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:

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### SpillerBD

##### Well-known Member
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
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
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

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:

#### Flashbond

##### Well-known Member
Is there anyway to get rid of SUMIF() also?

Last edited:

#### SpillerBD

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

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

Replies
0
Views
619
Replies
7
Views
179
Replies
1
Views
490
Replies
8
Views
145
Replies
1
Views
96

1,130,009
Messages
5,639,530
Members
417,094
Latest member
IsimiKehinde

### 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?

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