Combine Pivot Charts

[scott56hannah]

I have developed a pivot chart based on some weekly data for performance
analysis. The data fills almost a full worksheet (ie around 62,000 rows) each
week.

The pivot chart is great for our analysis on the previous week. But we now
want to combine those charts and look at weeks side by side and then for the
whole month.

I would like to overcome this restriction in Excel and don't want to have to
use an external database to store the information. Is there anyway this can
be done.....by programmatically referring to a number of worksheets in the
pivot chart calculations ?

Thanks
Scott

 

[Roger Govier]

Hi

Using Multiple Consolidation ranges in Excel can give results different to
those you expect.
Take a look at Debra Dalgleish's site for more information on this.
http://www.contextures.com/xlPivot08.html

Obviously with versions of Excel below XL2007, your data is too large to
append it all to one sheet. XL2007 has a capacity of over 1 million rows,
and in this package you could copy the data all to one sheet.

You could use separate PT's to carry out the analysis, and use GetpivotData
to pull the results form a number of worksheets to another location.
Don't use Pivot Charts, but create a Chart based upon this new set of data.
Again on Debra's site, there is information on GetPivotData
http://www.contextures.com/xlPivot06.html