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.
And here is the kind of pivot table I want to be able to make:
Thank you in advance for any help anyone can offer with this.
Best Answer
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!