Hello everyone, trying to find the solution to my problem, I found someone that has the same problem as me. Ill just paste what he wrote because I think he gave a great explaination on what he wants to do.
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.
piv1.png
And here is the kind of pivot table I want to be able to make:
piv3.png
I hope there is a solution for this. Thanks for reading, hope everyone have a great week!
Bookmarks