Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2006

Tip: Looking for answers? Try searching our database.

PivotChart will not preserve series formatting for some charts when reopening workbook

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
keeena - 21 Jun 2006 17:31 GMT
I have a WB with 4 PivotCharts.  When I close and reopen the WB, 2 of
my PivotCharts have slightly skewed formatting.  Can somone help
determine why?

Details:
PTable 1 / PChart 1, PTable 2 / PChart 2 all based on static data in
Sheet 1.
PTable 3 / PChart 3, PTable 4 / PChart 4 all based on static data in
Sheet 2.
All PTables were created from a new PCache object.  I'm using any
single data cache for more than 1 PTable.
When I reopen, PChart 1 and PChart 3 have the following problem:
- Series order goes back to the default order.
- Series colors remains in the old order.

1. I know that changing the underlying PivotTable will reset the
corresponding PivotChart's series formatting.  However, I am not making
any PT changes.  Just close and reopen the WB.

2. The PTable has PreserveFormatting enabled and Refresh Data is
disabled.

3. There are no volatile operations that would cause the PTable to
change (e.g. Date() function)

4. I know I could write code to re-set the formatting upon opening the
WB.  I would rather find the reason why some PCharts's are changing
upon reopening.  I'd be OK if either all of them changed (I'd just
curse Excel) or none of them change (really how it should be working as
far as I understand the known quirks with pivots).

TIA,
-K
keeena - 21 Jun 2006 21:47 GMT
Problem resolved.

I didn't know that PivotChart series order was strictly tied to the
underlying PivotTable.  Setting the SeriesOrder in the PivotChart will
work while the WB is open, but on next open, the chart reverts back to
the order as it exists in the PivotTable.  I guess this is by design -
I've never noticed this behavior before.

Solution is to change series order at the PivotTable.  This will bubble
up to the PivotChart and will retain after close/reopen.

A nice enhancement would be to automatically order the PivotTable when
its respective PivotChart series order is changed.  Or at least prevent
PivotChart series modifications so its more obvious.

Hope this helps someone else out there.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.