Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Using Pivot Table to consolidate multiple worksheets

I am attempting to use pivot tables to take rows of data from multiple
worksheets (with common column headings and layout) into one. Headings: "Proj
name" "Risk Title" "Risk description" "Priority" (Proj Name is there as it
seems de-normalising is a requirement for pivoting).
eg
Proj name Risk Title Risk description Priority
Proj4 Widget problems Description 12 High
Proj4 New legislation Description 13 High
Proj4 Cost overrun Description 14 High

I want the pivot table to be able to filter by Project Name or Priority.
I've used the 'consolidate from multiple pages' and ranges based on each
worksheet, including column headings.
However, when I create the table, I cannot get the columns to move into the
rows - they do not show up in the field list and they won't drag and drop
into rows. Is there a way I can make this happen? Or is there a smarter way
of achieving what I want? I've made it work when the data is in a single
range on one page, but not when split into various worksheets.
Thanks

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Using Pivot Table to consolidate multiple worksheets

Hi Dianna

Using Multiple Consolidation ranges does not work in the way you might
expect.
See the note on Debra Dalgleish's site about this.
http://www.contextures.com/xlPivot08.html
along with some suggested ways around.

The simplest method, assuming your combined data does not exceed 65000
rows (XL2003 and below), would be to copy and paste the data from all
sheets to one new sheet, and Pivot from there.

--
Regards

Roger Govier


"Dianna S" <Dianna wrote in message
...
I am attempting to use pivot tables to take rows of data from multiple
worksheets (with common column headings and layout) into one.
Headings: "Proj
name" "Risk Title" "Risk description" "Priority" (Proj Name is there
as it
seems de-normalising is a requirement for pivoting).
eg
Proj name Risk Title Risk description Priority
Proj4 Widget problems Description 12 High
Proj4 New legislation Description 13 High
Proj4 Cost overrun Description 14 High

I want the pivot table to be able to filter by Project Name or
Priority.
I've used the 'consolidate from multiple pages' and ranges based on
each
worksheet, including column headings.
However, when I create the table, I cannot get the columns to move
into the
rows - they do not show up in the field list and they won't drag and
drop
into rows. Is there a way I can make this happen? Or is there a
smarter way
of achieving what I want? I've made it work when the data is in a
single
range on one page, but not when split into various worksheets.
Thanks



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to consolidate multiple worksheets into one. Bovine Jones Excel Discussion (Misc queries) 16 February 20th 09 11:45 PM
how do I consolidate multiple pivot tables into one pivot table? pkahm Excel Discussion (Misc queries) 0 April 20th 06 09:48 PM
pivot table and multiple worksheets brianTmcnamara Excel Discussion (Misc queries) 1 February 3rd 06 06:16 PM
Pivot Table with multiple worksheets jo74 Excel Discussion (Misc queries) 2 September 27th 05 04:33 AM
Consolidate data from several worksheets via pivot table mthatt Excel Worksheet Functions 0 March 23rd 05 07:51 PM


All times are GMT +1. The time now is 08:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"