How do I create a pivot table from multiple pages of data?
 

I have data in a spreadsheet in excess of 65536 lines so I have the data on
more than one page in a workbook. How do I create a pivot table that will
use more than one page of data to create the pivot table? Is it possible?

Since you already have in excess of 65536 rows, can I assume that only
one (or zero) sheets will be updated?

If so, here's what I'd do:

On Sheet A, make a pivot table from the the 65536 row on Sheet 1.
Add to sheet A, below the previous pivot table, a pivot from the rows
on Sheet 2, continue down sheet A until you run out of space, I hope
thatyou don't.

Then create a table from the pivot tables. Set it several rows to the
right of the pivot tables. Use a formula to bring the data from the
pivot table to this offset table. Create all these table from the
multiple pivot tables in a contiguous range and then, create a final
pivot table off of that.

If you have trouble with the blank row headers in the pivot table, use
a formula like this: if(isblank(a1),"cell above this one",a1)

mw

You can create a Pivot Table from multiple consolidation ranges, but you
won't get the same pivot table layout that you'd get from a single
range. There's an example he

http://www.contextures.com/xlPivot08.html

If possible, store your data in a database, and base the pivot table on
that. You'll have more flexibility in creating the pivot table.


PT Cruiser wrote:
I have data in a spreadsheet in excess of 65536 lines so I have the data on
more than one page in a workbook. How do I create a pivot table that will
use more than one page of data to create the pivot table? Is it possible?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Instead of importing into Excel, you should import or link into Access. Then
when creating your Pivot Table in Excel, link to your database.

This is much cleaner, than creating multiple worksheets.

"PT Cruiser" wrote:

I have data in a spreadsheet in excess of 65536 lines so I have the data on
more than one page in a workbook. How do I create a pivot table that will
use more than one page of data to create the pivot table? Is it possible?