Pivot table across multiple worksheets

[StuartR]

I have a workbook that has a number of worksheets with the same layout - (one for each bank account or credit card).

I want to create a single pivot chart that includes data from all of the worksheets. At the moment I do this by copying every change to every worksheet and pasting it into a "consolidated" worksheet that includes data from every account.

Is there a simpler way to do this?

[HansV]

Excel can create a pivot table from multiple consolidation ranges. In Excel 2003 and before, this was one of the options in the Pivot Table Wizard, but Microsoft has cleverly hidden this option from the user in Excel 2007. Fortunately, the wizard can still be started by pressing Alt+D, then P.

x71.png

See Excel Pivot Table Tutorial -- Multiple Consolidation Ranges for detailed instructions.

[StuartR]

That was very well hidden. It works in Excel 2010 as well, but the limitations are so severe that I don't think I can use it. The main issue is that I can't control what columns are in the pivot table.

I have a column called "Month" that contains values like Apr09 and May09, that I was using as my column headings, and a column called "Category" that was the row headings. The categories are listed on the left as I would expect, but I have a single column called "Month" that appears to have summed all the month values!

[HansV]

The multiple consolidation ranges option is indeed limited.
Would it be feasible to use the consolidated sheet with data from all accounts as the "master" list?

[StuartR]

The multiple consolidation ranges option is indeed limited.
Would it be feasible to use the consolidated sheet with data from all accounts as the "master" list?

Is there any simple way to auto populate the consolidated sheet, as I currently copy and paste every update and I occasionally make a mistake!

[cshenoy]

Would it work for the PivotTable if you renamed the Categories in each sheet with the Category&Date? Then the columns would be Category for that date. You could filter by category also.

[StuartR]

The data is not structured like that.
Each line has a category (selected from a master list), a date, an amount, a running total, and a month (calculated from the date).
The pivot table shows the total I spent in each category for each month. It's amazing how much I spent on books in July and August!

[HansV]

Instead of working with separate sheets, I'd enter the data directly in the consolidated master sheet, with - if you don't have it already - an extra column for the account/card. You can view the data for a specific account/card by applying a filter.

[StuartR]

Instead of working with separate sheets, I'd enter the data directly in the consolidated master sheet, with - if you don't have it already - an extra column for the account/card. You can view the data for a specific account/card by applying a filter.

I could try this, but it would be hard to keep a running account total, which I need to reconcile my statements.

Edited to add: Yes I do have a column for account name.

[StuartR]

I think I am getting there. I can use a SUMIF on the account name to get the running total, I just need to store the opening balance for the account for adding on.
A few other details to sort out, but this looks like it will do the trick.

[HansV]

You could use SUMIF for the running sum per account. See the attached very primitive sample. (You'll have to refresh the pivot table in the second sheet, it now has Dutch month names)

Budget.xlsx

(I hadn't seen your latest reply)

[StuartR]

I have most things working the way I need them to, just one more to fix.

I used to keep a note of the balance in each account in a fixed cell, so that I could use it on another sheet. I used the following formula (from this lounge or another that I used to belong to).
=LOOKUP(9.99999999999999E+307,$F:$F)

I need to modify this so that it only looks at cells with a specific account name in column H, so that I get a separate total for each account.

[HansV]

As an array formula (confirm with Ctrl+Shift+Enter):

=LOOKUP(9.99999999999999E+307,IF($H:$H=P1,$F:$F))

Where P1 contains the account name.

[StuartR]

=LOOKUP(9.99999999999999E+307,IF($H:$H=P1,$F:$F))

Works perfectly, thank you.