Pivot Table

[SiouxieQ]

Hi there,

Just a quick question to see if something can be done.

Can a pivot table be created from data in two seperate sheets?

Or is it best to export data to access and create some links and manipulate
it in queries there?

Cheers,

Sue

 

[JulieD]

Hi Sue

quick answer - yes
in the wizard choose multiple consolidation ranges
however, there are some limitations to how the data from multiple ranges can
be manipulated so you might need to give it a go to see if you can get the
outputs you want.


Cheers
JulieD

 

[Wally]

I too am seeking a way to use multiple worksheets (3 in fact) as the data
source with the PivotTable Wizard.

I have tried the Multiple Consolidation Range option and it makes a huge
mess out of what I'm trying to do (plus it does create a complete PivotTable,
stuff gets left off).

There should be an easy way to add multiple sheets in the wizard OR add to
the field list from other worksheets than what was used to create the initial
field list.

I've gone so far as to look into using VB to create the pivot table with
multiple worksheets but I'm not getting very far...

 

[Debra Dalgleish]

There's information on using multiple consolidation ranges here:

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

If you can use on table in Access, to store the data, that might be
easiest for you, then create the pivot table in Excel.

If you need to keep separate tables in Excel, you can use MS Query to
join the data, and create a normal pivot table. There's a sample file here:

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

Under Pivot Tables, look for 'Multiple Consolidation Ranges'