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.