Multiple worksheets data to one pivot table

[TJ]

I previously created a workbook in excell 2000 it had 16 pages of data. I
created a pivot tabel to gather all data in one report. It mostly had totals
for scores with multiple players at multiple locations with totals for each
player at various locations. Even I'm confused after that. Now I had to
reload windows because of a virus my kids got from downloading misic. I had
to recreate all the documents. I now have Office 2007 and can't figure out
how to get the data from more than one sheet. Please give me step by step
instructions on how to get data from 16 sheets into one pivot table all from
the same workbook.

Thanks TJ

 

[Ashish Mathur]

Hi,

Somehow the "Multiple consolidation ranges" feature of picot tables do not
pivot the data, the way we would like it to - I therefore understand your
problem. You could try using the following trick.

1. Insert a blank sheet and type the columns heading (for data which which
we will pull from the child "sheets"). Say the headings are in A2 and B2;
2. Click on cell A3 and Go to Data > Consolidate;
3. In the reference box, select the range of data on the first sheet and
click Add;
4. Please repeat step 2 for all the sheets
5. Check the box for "Create Links to Source Data"
6. In the function box (at the top), select Count
7. Now click on OK

You will now see grouped rows and when you ungroup them (by clicking on the
plus sign), you will see all the data from the individual sheets. However
you will also see data being summarised by the COUNT function which we do
not need. This is the procedure you can use to remove all the COUNT rows:

1. Select any one column of the range;
2. In the Replace box (Ctrl+H), find COUNTA( and replace with COUNTAF(. Now
click on OK
3. All the COUNT() functions will be replaced with errors;
4. Now press Ctrl+G > Special
5. Select the formulas radio button and uncheck all boxes except errors
(This procedure will highlight all errors) and click on OK
5. Now simply do Alt+E+D+R ( to delete all rows which are selected)

You will now see the sanitised data before you which you can pivot.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[ShaneDevenshire]

Hi,

There are two pivot table ways to do this:
1. Create an Outer Join
2. Use Multiple Consolidation Ranges
both of these are rather tricky.

If you did it in 2000 it suggests to me that the data layout might be
crucial. Can you show us how the data is laid out on one of the 16
individual sheets?