Pivot table

nekuchi

Hi all

I've been all over the internet trying to get help on this but no luck.

I was wondering, is it possible to create a pivot table of other pivot tables? I have 12 tabs in a spreadsheet each with a pivot table and I want to consolidate the data from each individual pivot table into one overall pivot table.

If it's not possible, what's the best way to go to get that info?

Thanks for any help

PS, version is excel 2013

Dravokivich

As far as I know, a pivot table only reads from one source. So you'll need to find some way of arranging all the results from the other pivot tables, into one table.

You said 12 sheets. Is that for 12 months? I use a pivot table to present certain parts of data for months throughout the year and I find it easier to have a column with the month in my source.

This' ll also be a bit messy if all your pivot tables have different headers...

Thoie

You can use a pivot table to consolidate all 12 of the sheets if you like.

http://office.microsoft.com/en-ie/excel-help/consolidate-multiple-worksheets-into-one-pivottable-report-HA010226585.aspx?CTT=5&origin=HP010095249


Using Alt-D-P to call up the wizard, select "Multiple consolidation ranges".

I haven't used it, so I'm not sure how it works, but hopefully the help link above will, erm, help.

nekuchi

That looks like the link I've been looking for, I'll try it later and let you know. So far, all pages I found refer to 'power pivot' which I don't have but this one doesn't!!! Yay!
Will let you know how it goes...

nekuchi

I tried using the Alt-D-P to create the pivot table and it seemed to work, until I tried to open it the next day when I found the file had been corrupted. I managed to salvage most of what was in it and with only a little work it was back to what it was. Later I found out that there is some issue with 'show properties in tooltips' in pivot table options. Can't be sure that was the cause though.

I'm guessing that I'll just have to manually add them together, sigh...

Thoie

To be honest, if you have the source data (I presume you do), I'd consolidate all the sheets (unless they'd exceed the size limit for a sheet) using formulas/macros and run my pivot from there.

If the data is too much for a single sheet, then loathe as I am to recommend it, you may want to look at keeping this information in Access.

If your sheets are all separate months, putting it into a database will give you a lot more flexibility. Instead of multiple pivot tables, you'd have one query that you could run on different date ranges. E.g. here's the January results, here's the Jan-Mar results, here's the Jan-Dec results.

nekuchi

Cheers Thoie

I found a possible workout similar to what you suggested. I consolidated data into a table (Data->Consolidate) and created formulae for the info I wanted. Access isn't an option here, we don't have it. Long way round is better than no way round.

Cheers for all the help.