get values from a table and calculate in another

[Dineth]

I have a table, stored data of foods and prices (product description table)
I have another table. it has product name and quantity bought. I need to create a column for expense. (price of the product from product description table * quantity from expense table)

can I do that? I am using excel 2013. an example is attached herewith.

Thanks.

[TMS]

Maybe this:

Please Login or Register  to view this content.

Regards, TMS

[Shareez Saleem]

Use this formula in M16

=L16*INDEX($F$9:$F$14,MATCH(K16,$E$9:$E$14,0))

[TMS]

@Shareez Saleem: when the OP has all those Structured Tables, it seems a shame not to use Structured Table references

[Shareez Saleem]

Let him understand it in a simple way.

[newdoverman]

Select the table with Product Price and Category then click on the Formulas tab, Defined Names, Create from Selection. Choose Top Row and Left column. This will give you names for all of the products and the price of each.

In M16 enter this formula and fill down the column of the table.

Please Login or Register  to view this content.

This will use the product in K16 (which is a defined name from the above procedure). The INDIRECT function makes the value in K16 a value to be recognized as a name. There is a space between the INDIRECT(K16) and Price. This creates and intersection in the table that has a value. This value is then multiplied by the value in [@Quantity]. This results in a formula that is Product Price, times Quantity.

[Dineth]

Thanks all. newdoverman you are great.

[Dineth]

Select the table with Product Price and Category then click on the Formulas tab, Defined Names, Create from Selection. Choose Top Row and Left column. This will give you names for all of the products and the price of each.

In M16 enter this formula and fill down the column of the table.

Please Login or Register  to view this content.

This will use the product in K16 (which is a defined name from the above procedure). The INDIRECT function makes the value in K16 a value to be recognized as a name. There is a space between the INDIRECT(K16) and Price. This creates and intersection in the table that has a value. This value is then multiplied by the value in [@Quantity]. This results in a formula that is Product Price, times Quantity.

This is the nicest mtd I have seen so far. However in the pivot table I can't use expense as a number column. If you can fix this, would be a great help.

[newdoverman]

Your pivot table must have been created in Excel 2013 as I cannot reproduce it in Excel 2010. I however did produce a Pivot table that did include the expense column and it worked perfectly. I can see no reason that it won't work for you. You may have to rebuild the pivot table to get it to work or refresh the Data Sources as there are more than one.

[TMS]

Not sure what you are trying to do ... but this updated sample workbook has a Pivot Table created in Excel 2007.

Regards, TMS

[Dineth]

Thanks. but your pivot table has only one data table in it. I need all three tables.
Let's say I need to get expenses by gender. Problem with the formula is it gives expense as text values when made the relationship. Pls see the image.
Capture.JPG

[Dineth]

Your pivot table must have been created in Excel 2013 as I cannot reproduce it in Excel 2010. I however did produce a Pivot table that did include the expense column and it worked perfectly. I can see no reason that it won't work for you. You may have to rebuild the pivot table to get it to work or refresh the Data Sources as there are more than one.

as far as I am using a pivot table for one data table, everything OK. but the idea of this whole work is building relationships among different tables and make a data model.

for an example I need to divide expenses by gender. (in pivot table gender in raws, expense in values) can you do that? mine is giving an error...

Thanks.

[newdoverman]

In order to do what I think you want with the Pivot table using Excel 2010, I had to incorporate columns from the other tables into the main table. I added the columns in the main table then by formula extracted the data from the other tables.

[Dineth]

In order to do what I think you want with the Pivot table using Excel 2010, I had to incorporate columns from the other tables into the main table. I added the columns in the main table then by formula extracted the data from the other tables.

ha ha ha .... objective of the study is to avoid this. so we DO NOT need to store all the unnecessary data (repeatedly) in one big table. instead we can store nice and neat separate tables for people, food and expenses AND analyze data using any variable from any table as the need be.

Thanks anyway. Appreciate your time and effort.

P.S and your named range thing did work. so as far as expenses is number column, (not a calculated column ) I can do pivoting with all three tables.

[newdoverman]

Well what you have in your workbook is produced in Excel 2013 and the same thing isn't available in Excel 2010 so cannot be produced in the same manner. Someone with Excel 2013 might be able to help you out.