Custom Data Labels does not update with correct labels in a dynamic Scatter XY Plot Graph

[kujikuru]

I have used a macro to add data labels to a scatter plot.

I initially tried the add-ins which people have been recommended on this forum, but they do not work with my data,

Basically, I have a dynamic table that a user selects to display the data they need. All the data points are displayed from one dynamic table into one dynamic XY Scatter graph/plot.

For example, sales data by region (North, South, East and West) will show data points in the scatter plot for each region depending on the criteria set by the user from a drop-down menu.

The scatter graph has a named range offset formula to ensure that it only reads the data in the columns and not the whole column. For example, North may have 50 sales lines, yet South has 30. The offset named range is added in the series when selecting the data so when North is selected, the range of the scatter graph includes 50 plots, but when South is selected it will change the range of the chart so it only includes 30 data points. This ensures the graph plots the data correctly and it works a charm (Thank You Mr Excel!! Excel Dynamic Chart #10 YouTube Vid tutorial).

My problem is that I used someone's macro to add data labels. It works too. But, because I have a dynamic table, it does not take into account that my data labels and series range will change according to criteria.

For example, it will correctly apply the data labels to the data points in the North Region. Change the region to South, the data points will change, but the data labels are being used from the North Region, not the South. That means I have the correct data points being plotted, but the wrong data labels (as it uses the first criteria data labels.

I need a macro that will update the data labels every time the criteria changes.

I do not want to use add-ins as they do not do what I need them to do.

This is the macro I used. I got the VBA code from a tutorial blog http://www.wiseowl.co.uk/blog/s130/e...ls-problem.htm

Please Login or Register  to view this content.

Any help would be greatly appreciated!!! Thanks in advance!!

[Andy Pope]

Hard to tell without example but I think the problem is that data label references are static.

See this explanation and whether you can adapt you workbook
http://www.andypope.info/ngs/ng57.htm

[kujikuru]

thanks andy.