Automatically Name Series in Chart

[JimM]

Hi
I Have a table of data that I'm using to create a clustered bar chart. Each row in the table is a series, and the columns C:P contain the series values. The chart works great but the legend for the series names just says series1, series2 etc ColumnB actually contains the series name but I can't work out how to get the chart to automatically pick this value up. I know I can go into the Select Data and edit each name manually, but the chart will have a new row added each week and don't want to have to edit the new series every time.
Is there a way to identify column B as the series name column?
Hope that makes sense!!
Thanks

 

[JimM]

I've managed to create a solution to my issue, not sure it's the best way but it's a workaround that works, basically a bit of code that counts the number of series, and then loops through them and names them according to the cells in columnB


Dim LngSeriesCount As Long
LngSeriesCount = ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection.Count

For i = 1 To LngSeriesCount

ActiveChart.FullSeriesCollection(i).Name = "=Sheet1!$B$" & i + 1

Next i

 

[Jon Peltier]

Did you select the series names in column B when you created the chart? Excel is pretty good at picking up series names.

How is the row added to the chart each week? Is the data in an actual Table? Then the added series should also pick up the series name in the added row.