Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel series with datalabels from different column (Excel xp)

    I have a graph with a bar that is the 4th series in my graph. The bars go to values of 500, 1900, 300 etc. The user, however, want the datalabels to be divided by 10. The bar will go on a "Y" axis using their real values of 500, 1900, 300, BUT the data la bels must read 50, 190, 30. I am making the graph in vba and i don't know to do this. Can anyone help me? thanks you very much.

  2. #2
    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: Excel series with datalabels from different column (Excel xp)

    I don't know how detailed you need so I'll be lazy:

    Add the chart label values as normal (they will be 500, 1900, 300)
    Change the chart labels for the series to the value/10 (or just hard code it to be 50, 190,300)

    One caveat this will not be "live"
    If you need it live make a column with the values /10 referenced from the source)
    Change the data labels to be linked to those cells ("=Sheet1D1" or whatever)
    Now if the data changes the labels will still be the value /10.

    There are some addins available which allow changing datalabels if you don't want to code it yourself. You should be able to seach the postings to find it.
    Steve

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel series with datalabels from different column (Excel xp)

    Hello,

    Your post said you wanted to do this in VBA, so I have created an answer that might work for you.
    It works for me, but since you did not post a sample of your code, its hard to tell...
    -------------------------------

    Sub ChangeTheLabelValues()
    Dim WorkingChart As Chart
    Dim SeparateLabel As DataLabel
    Set WorkingChart = ActiveSheet.ChartObjects(1).Chart

    For Each SeparateLabel In WorkingChart.SeriesCollection(4).DataLabels
    SeparateLabel.Text = CLng(SeparateLabel.Text) 10
    Next

    Set SeparateLabel = Nothing
    Set WorkingChart = Nothing
    End Sub

    ------------------------------------------------

    Jim Cone
    San Jose, CA

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel series with datalabels from different column (Excel xp)

    Download Rob Bovey's xychartlabeller from:

    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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