Link text boxes in chart to data point to move WITH data EXCEL

[Louise99]

I have a graph where a have text boxes with one, two or three stars in various color above certain data points (months). This is a rolling 12-month chart. When a new month comes around I have to move ALL the text boxes because they're not attached to the data point.

I tried to use "data labels - values" and change the values to my stars but they didn't move with the month they were attached to.

Any ideas?

Thanks!

 

[Rofeu]

Hi,

what you could do is to have a new series that has only the same values as the other series but only for the values that you want the stars for. For that series you go to 'format data series' and set the data labels to values and under 'options', set the property 'overlap' to 100.
Then for the data labels set the numberformat to e.g. "***". (you can delete the legend entry for this series).

That's the only way I can think of without using VBA.

Cheers,

Roel

 

[Rofeu]

hmm..missed the colour and varying number of stars part.

Instead of setting the format to "***", you can go to Special and select the option 'Linked to Source'. Now in the range of the new series, you can set a custom format like "**";"***". This will give 2 stars for a positive number and three for a negative, which, depending on your conditions you could work into a formula in the cells for the second series. As for the colours, you could include, in example, [red] or [black] in the custom format (there are more than these, but not all colours are available).

You didn't specify the conditions for when a point get 2 or 3 stars and how the colours are determined, so I can't really tell you anything more than this.

Cheers,

Roel

 

[Louise99]

That first idea didn't work out, unfortunately. Do you know a simple macro that might work - or am I asking for too much help? I appreciate it.



Thanks!

 

[onedtent]

do similar to what rofeu has suggested. add second series but with a constant (where u need stars) such as 20 or whatever suits u. plot the chart as a stacked chart. have your stars available as jpegs or gifs. click on your data point once and then again. right click on your data point, choose format data point. under patterns set borders to none. under area click "fill effects", picture tab, browse to your stars, select, insert, click ok twice

- onedtent Onedtent OnedTent

Trying not to answer posts that have been replied to