Use Joiner Tables Between Tables

March 06, 2023 - by Bill Jelen

Problem: I have a top-level budget table with one row per month, region, product. It has 54 rows. I want to create a report comparing the budget table to an invoice table with hundreds of rows.

Strategy: Create three tiny “joiner” tables to act as intermediaries between the two tables. In the figure below, the three row Product table is a joiner table. Build a relationship from ProductB in the Budget table to Product_J in the joiner table. Build a second relationship from ProductA in the Invoice table to Product_J in the joiner table. Build similar relationships, joining Region_J to both RegionB and RegionA. Also create relationships to join the Date field to both DateB and DateA.

With a small budget file and a large actuals file, you need three joiner tables to talk to both tables: Product, Region, and Calendar — Figure 1051. Add extra tables to act as intermediaries between Budget and Invoice Detail.

When you build your pivot table, all fields in the Rows, Columns, and Slicers should come from the Joiner table.

All Row, Column, and Slicer fields should come from the Joiner tables. — Figure 1052. The pivot table works if you use fields from the joiner tables.

This article is an excerpt from Power Excel With MrExcel

Title photo by Guillaume Bourdages on Unsplash