Another option may be to use a UNION statement in Access to create a
view that consolidates the data from the multiple worksheets and allows
the Pivot Table to connect as if it is looking at data in a single
sheet.

a post at
http://www.excelforum.com/showthread...ighlight=union
describes how to do this for convering data in multiple columns in a
spreadsheet into a single column but I see no reason why it would not
work connecting multiple worksheets. I have not tested it... but it
can't be worse than trying to use 'multiple consolidation ranges'

The process would be to create an Access DB, then create a linked table
for each page with data in your source spreadsheet, then write the query
that does the union of all the linked tables. In another spreadsheet you
can then create a pivot table using 'external data' and attach to the
query. You will then see all data as if it is in one table and can do
whatever you need to do in the pivot table.

If you are going to have to do this more than once I expect it will be
worth the effort.

regards...


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=526081