MrExcel.com - Your One Stop for Excel Tips & Solutions

Pivot Table from multiple worksheets

Posted by chris on January 24, 2002 4:02 PM

Is it possible to create a pivot table from multiple worksheets somehow? I have data that spans more than 65000 lines.

Thanks,
Chris

Posted by Charlie Pellatt on January 29, 2002 1:34 AM

I'm analysing data all the time with datasets in excess of 65K lines and have resorted to using OLAP cubes which can be created using MS Query where you ODBC into a data source that can accommodate more data (eg Access, dBase, SQL etc).

MS Query is an excellent and under used component of Excel that can create OLAP cubes from many data sources (you will need the correct ODBC driver). A wizard guides you through the creation of the OLAP cube which can then be used as the data source for the Pivot Table by using Data, Get External Data, New Database Query and selecting the OLAP cubes tab.