Pivot tables using multiple tables


New Member
Sep 30, 2014
I building a database to practice my excel skills and I have compiled three small excel tables which I am going to expand once I get the basic structure and calculations working. I'm also simplifying things just to make it easier to understand my problem. I think the formulas are going to end up being rather complex but I think I can handle them.

Table1 is a list of locations and has columns with different properties based on that location (think of it like tax rates etc). Table2 is a list of products with different properties based on those products (think of it like base cost, dimensions, etc). Table3 is a how the first two tables relate to each other. The first column is a list of all the locations and the top row is a list of all products. The intersections in the table for the locations and products are either "TRUE" if the location carries that product and I just leave it empty if not.

So I am trying to set up a pivot table so I can do some analysis. In my pivot table, I want the rows to be a list of the products, but I want a filter to only show the products that are "TRUE" at the selected location. I also want to have it provide a value for each row in the pivot table using a custom formula that needs the properties from the selected location from Table1 and properties from the product corresponding pivot table's row from Table2.

So far, I have set up the pivot table using the "Data model" selection. Then I moved the three tables I am using to the Active Fields. I've also set the relationship between the first column of Table1 and Table3. I don't seem to be able to set a relationship between a row and a column for Table2 and Table3. So, I'm kinda stuck on what I need to do next. I am not sure how to make the filter work. Or how to add custom formulas for values. I feel like I could just use indirects, vlookup, and hlookup for most of the formulas, but I'm not sure how to enter custom formulas into the Values field.

If my question isn't clear, please let me know and I'll try to clarify it. I appreciate the help and thank you for your time.

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics