Conditional text in chart

[hedgie]

I prepare charts every month based on performance. Until the performance is finalized, each chart must have 'EST." added to the final data label. There is a simple "yes/No" dropdown box that indicates whether the data is an estimate or final. Is there a way to automate the addition of the text into the last data label of a series? Currently I simply add a text box to each chart manually - the issue is there are quite a few charts and secondly it is very easy to either miss adding or removing these boxes when appropirate.

Thoughts?

 

[MarkAndrews]

Why dont you reference your chart title to the validation cell which contains Est 'Yes' or 'No'

Click the chart title & reference to a cell, in that cell type your chart title

="Chart Title "&A1 (A1 being your validation cell)

This way the chart title wont change, but each time the validation cell is changed it will read - Chart Title & Yes (change as required)

??

 

[hedgie]

This would highlight if I need to do a manual change - which is helpful, but has the problem of having the estimated chart having an incorrect title.

Is it possible to reference the data series label to a cell?

 

[hedgie]

I found a possible solution, but I can't make it work. I think if I use multi-level categories i can get the right effect. Rather than data labels I would use the sub category to equal the data label values plus &" Est." where applicable. The problem is that the subcategory column is not adjacent to the main category column. Is it possible to properly format the data selection to make this work?