Sticky Labels and Sort (2002 SP3)

Topic

New Reply

Your attachment hasn’t made it to the post. If you previewed your post, the attachment box was emptied; you can edit your post and add the attachment. If it is anExcel file over 100 KB, create a zip file and attach that.

Reply | Quote

Replies

Thanks Hans, I can’t blame wopr here, I completely forgot to attach the file!

Now attached to the original post.

Reply | Quote

You could use a macro:

Sub UpdatePivotChart()
‘ Update and resort the pivot table
With Sheets(“Sheet1”).PivotTables(“PivotTable1”)
.PivotCache.Refresh
.DataBodyRange.Sort
End With
‘ Fix the data labels
With Sheets(“Chart1”).SeriesCollection(1).DataLabels
.Position = xlLabelPositionCenter
.Orientation = xlUpward
End With
End Sub

Reply | Quote

True,
I guess I realised that, but that doesn’t really cause the formatting to “stick”, rather it automates correcting it each time.

I was expecting that there would be a way to tell excel that this is the formatting I want regardless of the data that it is charting.

Reply | Quote

Perhaps there is, but I don’t know it, so I had to resort to a macro.

Reply | Quote

I recall (if memory serves me correctly) a similar post a while back in the Excel forum. It dealt with preserving formatting on a pivot table (not the charts). I think there may have been a solution offered there, but I cannot locate the thread. I cannot remember what the solution was, but I recall it answered the posters question, and preserved the formatting in the pivot table.
Paul… if you have chance, attempt to run some searches in the excel forum using different criteria, and see if you can locate the thread…as it may contain an answer for you!

Reply | Quote

Were you thinking of Pivot Tables – saving formatting changes?? (Excel 2003)

Steve

Reply | Quote

Thanx Steve, but nope!
I remember this one, but I strangely recall another post that dealt with this as a side topic in a thread dealing with something else, and its this thread I was trying to locate without much luck!
Come to think of it….I may have read it in some other source??? (Not at Woody’s)

Reply | Quote

Hi again,
The attached sheet has a pivot chart with data labels formatted as Position Center and Orientation 90 degrees.

However, every time I refresh the data the Labels lose this formatting.

How do I make this formatting stick?

Also, the pivot table is sorted on the Count column. when I add new data and refresh the table, the sort order is lost.

Can I make this stick too?

TIA

Oops, I completely forgot to post the attachment!!!

Reply | Quote