Q: How to make Dynamic Bubble Chart?

[jg70124]

XL 2002

I've got a bubble chart in which the number of bubbles will change over
time.

I know that if I put the bubbles in a single series, then the number of
bubbles can grow automatically. But then I won't be able to dynamically add
the correct labels. (I know from reading the messages in this ng that there
are add-ins which will do the labelling. However, the spreadsheet with the
chart in it will get distributed to students at several different companies
and I don't know that they'll be able to install the add-in).

If I put each bubble is a separate series (so that the bubbles can be
labelled correctly), I can't figure out how to dynamically add new series as
new data is added.

Is there a way to do this without requiring an add-in?

Thanks.

 

[Jon Peltier]

You can define dynamic range names for the X values, Y values, and
bubble sizes, and the chart will automatically update. Unfortunately it
won't work with labels.

The best you can do is make the chart's data range much larger than you
need using dummy data, and apply the labels to the dummy points. Clear
the dummy range (don't delete any cells, just the contents), and the
blanks won't clutter up the chart. As data is added, the points and
their labels will reappear. As long as you don't need more points than
you preloaded, this will work fine.

- Jon

 

[jg70124]

Jon,

Thanks.

I know one of the labeling tools is yours. If I use that add-in to create
labels, then send the spreadsheet to someone who doesn't have that add-in,
will the labels still work?

Thanks.

 

[Jon Peltier]

Wrong John. But anyway, the addin is only required to put all the labels
in place. In fact, you could do it manually, but that gets old fast.
Rob's addin automatically inserts a link to a worksheet cell, and John's
gives you a choice. Those links remain, no matter what is in the cells,
as long as the cell or range isn't deleted.

- Jon