1

I have two named tables on separate Excel worksheets in the same Excel 2010 workbook. These tables contain the exact same header items. I could get the results I want by copying the data from one of the tables, pasting it at the bottom of the other and then making a pivot table off of that.

However, for business reasons, this is not a good option, and I instead need to create a pivot table that can produce this same result, while leaving the source data in the two separate tables.

I have tried using the legacy PivotTable and PivotChart Wizard with multiple consolidation ranges to do this, but I ended up with fields called "Row", "Column" and "Value" instead of fields for each of my header categories.

Here is what my data looks like. John's Deals and Sue's Deals are set up exactly the same. The Deal Manager is always John or Sue, matching the worksheet name. enter image description here

And here is the kind of pivot table I want to be able to make:

enter image description here

Thank you in advance for any help anyone can offer with this.

Liza
  • 113

3 Answers3

3

Another option if you're running an up to date version of Excel is to use PowerPivot, which is an optional add in to the latest versions of Excel. It's a steep learning curve, and I'd recommend learning the basics (Excel is Fun is a good place to start). I had a similar issue in creating an easily updated log for several different teams which could then spit out a report that would cover both individual, team and group performance. Several different tables with different pieces of data on them but the same fields, one report required. It works like a charm!

DavidPostill
  • 162,382
Miller86
  • 238
2

You can also do this using Power Query (add-in in Excel 2010 / 2013, built-in Excel 2016) to create data connections from your tables and then create a new Append Query that combines the two data connections into one table you can base your Pivot Table on.

NetMage
  • 202
1

I know this is old, but it is easy to do.

  1. Open the Pivot table wizard.
  2. Select "I will create the page fields"
  3. Add the different ranges.
  4. Select 1 for how many page fields you want
  5. Click on the first range, and then type in the page field name you want. EG The sheet range "John's deals" call "John", the sheet range "Sue's deals" call "Sue".
  6. Finish the table as normal and then move the pivot labels to suit.
user656646
  • 11
  • 1