Results 1 to 9 of 9
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sticky Labels and Sort (2002 SP3)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sticky Labels and Sort (2002 SP3)

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

    Now attached to the original post.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sticky Labels and Sort (2002 SP3)

    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!!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sticky Labels and Sort (2002 SP3)

    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

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sticky Labels and Sort (2002 SP3)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sticky Labels and Sort (2002 SP3)

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

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sticky Labels and Sort (2002 SP3)

    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!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sticky Labels and Sort (2002 SP3)


  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sticky Labels and Sort (2002 SP3)

    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) <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •