Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple worksheets data to one pivot table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple worksheets data to one pivot table
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 "TJ" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple worksheets data to one pivot table
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? -- Thanks, Shane Devenshire "TJ" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table and multiple worksheets | Excel Discussion (Misc queries) | |||
Pivot Table with multiple worksheets | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple Worksheets | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple Worksheets | Excel Worksheet Functions | |||
Pivot table from multiple worksheets . . . possible? | Excel Discussion (Misc queries) |