Pivottable from two or more sheets...

[papegoja]

Hi all !

Can anyone help me and explain how I get a Pivottable from more tha
one range/sheet?

I have more than 100.000 of sales figures (rows) that I want to make
pivottable out of.

But as we all know, Excel only handles 65536 rows. So I have to impor
the data two more than one sheet.

Is this doable? I am using Excel XP.

Best regards
/Dan

 

[Peter Atherton]

Dani

AFAIK this is not doable within Excel. Can you export both
tables into Access. You can create a cross tab query in
Access which is like a Pivot Table.

Regards
Peter

 

[Dan]

You can also create the pivot table directly from your
datasource without copying the source into excel. Just
choose external data source when making the pivot.

-Dan

 

[Debra Dalgleish]

You could create a Pivot Table from multiple consolidation ranges:

1. Choose Data>PivotTable and PivotChart Report
2. Select Multiple consolidation ranges, click Next
3. Select one of the page options, click Next
4. Select each range, and click Add, click Next
5. Select a location for the PivotTable, click Finish

However, you won't get the same pivot table layout that you'd get from a
single range.

For example, if customer is the first column in your data source, the
row heading should show the customer names. If remaining columns are
Units Sold, Product#, Unit Price and Total, the column area will show
each of those headings. You can change the function that's being used by
the data value, but it will use the same function on all these columns.

The Pivot Table would contain some meaningless data, such as sum of
Product# or columns full of zeros if the database columns contain text.
To avoid this, you can rearrange your database columns, and then use
data ranges that only include the columns that you want to total.

If possible, create your PivotTable from the original data source, or
store it in an Access database table..