Hi,
I am trying to create a pivot table from multiple worksheets. My problem is I can create a multi-source pivot table with date columns and $ amount values, but I am unable to populate the rows with different category options.
Current Workbook Setup
- 10 worksheets representing different spending accounts. All worksheets are formatted the same, with columns for date, $ amount, category
- For each data entry, the user can select 1 of 50 categories. Categories are the same for all worksheets. **This category information is what I am having trouble creating in pivot
- In total, each worksheet has 1,000+ entries
Problem / Goal
- I am trying to create a summary tab that will sum up the data from all 10 worksheets. I do no care about which data comes from which worksheet
- I want a multi-source pivot table that is organized by month/yr in the column, by category in the row, and summed $ amount as the value
- I can create a multi-source pivot table, but I can't get the row categories
Deal Breakers
- All account worksheets must remain separate sheets. I know combining into 1 sheet would solve my problem, but would create too many other problems
I always appreciate your advice. The lag using sumproduct is killing my current file. If you have any other ideas, I'd love to hear them.
Thanks!