Pivot Tables & Multiple Worksheets

  • Thread starter Thread starter JamboUK
  • Start date Start date
J

JamboUK

Hi,

I know a quite a bit about the basics of Excel but cannot claim to b
an expert when it comes down to the more advanced functions.

I am trying to get to grips with Pivot tables and have the basic
sorted.

The problem I am having is the vast amount of data I am manipulatin
means that it has to run onto two worksheets (I have exceeded th
number of rows available to me on a worksheet). I have therefor
created another worksheet containing the remaining data (same format a
sheet 1).

I need the data from both worksheets compiling into one pivot tabl
using the headings that I have created on the worksheet.
I thought it might be possible via the 'Multiple Consolidation Ranges
option in the Pivot Table wizard.

This allows me to select the data from both work sheets but doesn'
carry over the headings (therefore not making it possible to create m
Pivot table).

Is it possible to do what I want? Can anyone help? Thanks.

J
 
Hi

If you have so many rows of data, it might be a good idea
to use Access (excel sheets normally can easily be
imported in Access). Once you have your data in an Access
data base, you can make your normal pivot table in Excel,
by selecting "External data source" in step 1 of making a
pivot table.

Hans
 
You can also just do a "Crosstab" query (Basically same as pivot table) while in
Access and export to Excel if you want to keep it in Excel.
 
What you want to do is take ur excel spreadsheets and
upload it into access.

Then in the pivot table wizard. i think its step one where
you want to get ur data from ....hit get data from
external database.

then just pick access and use that table....thats the way
i do it.
 
Back
Top