Excel series with datalabels from different column (Excel xp)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Download Rob Bovey’s xychartlabeller from:

http://www.bmsltd.ie/mvp%5B/url%5D

Edited Mar 13th 2004 to update link

Reply | Quote