Using Pivot Table to consolidate multiple worksheets

[Guest]

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

 

[Roger Govier]

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.