Thread: XY Scatter Graph

Hi all,

I am working on a XL Scatter Graph that will automatically update as changes are made to the data. I would appreciate any help with this query.

There are three columns of data, with headers: "item name", "cost", "impact". Each "item name" is the legend or data label of the graph. "cost" is the x axis. "impact" is the y axis.

So, for each "item name", a point will appear on the graph showing the items cost and impact. If any chages are made to either its cost, or impact, the graph will automatically update.

Now, there might be five items that are shown on the graph. If I add a new item to my data it will not appear on the graph, unless I go into the source data, and add a new series.

Is there a way that new items can appear on the graph automatically?

Many thanks, Tom.

Adding new series to a chart is best done via code.

If you did not have the complication of naming each point with text you could have used a single series and a named range formula.

Try this,

It uses a listobject as the data source. Single series so data points will automatically appear.
The code part simply updates the data label text to that of the information in the listobject

if you select a cell in the table you should see a blue border appear which will include a new records * marker.

Or just type in the cell at the end of the list and it will expand

Sorry just noticed your using xl2000.

Listobjects did not exist back them

Here is the file with dynamic named ranges and the code adjusted for xl2000.
Select chart and press button

I'm assuming the error is related to locating the range for the data.
So something like this that specifies exactly which sheet to look on.

    Set rngData = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns(1)

Originally Posted by Andy Pope

Try this,

It uses a listobject as the data source. Single series so data points will automatically appear.
The code part simply updates the data label text to that of the information in the listobject

Hi Andy,

I stumbled across this after researching a solution to creating a dynamic xy chatterchart with labels.

I tried to use your macro, but when i delete a row, the area of the graph does not move automatically, distorting the data. Is there a way to make it move up automatically, just as you have made it move down?
I use Excel 2007.

Thank you