Condintional Format in Dynamic Chart

Question

I need help getting conditional formatting to correctly update the format of data labels.  I have a chart with dynamic values populated using vlookup.  The formatting for the x-axis values is also dynamic, using conditional formatting to switch from percent to whole numbers depending on what data the vlookup returned.  The conditional formatting works fine in the cells of the spreadsheet, correctly switching between percent values and whole numbers.  However, within the chart, the data lables do not change properly, even though the 'linked to source' box is clicked.  The data label format on the chart remains as whatever the background format of the cells are in the spreadsheet, even when conditional formatting is producing the correct format. 

Can you tell me how to get the chart's data labels to respond to the conditional formatting of the cells feeding it?

Thank you!

Answer 1

The linked to source refers to the cells direct formatting and not the result of conditional testing.

To have the data labels reflect the correct formatting you will need to use named range to reference the cells.

Example file. You will need to down load it due to limitation of Excel web apps.

https://skydrive.live.com/redir?resid=EAD861C14BCC56B7!728

Answer 2

@David,

Is that the example I provided does not work or your implentation of the technique?

Answer 3

Hi there, 

this didn't work for me - the graphs are still not dynamically updating the label formats. 

any thoughts?

thanks

David

Answer 4

Thank you Andy --this resolves the issue.  I see the reason this works is because the range naming feature allows different cell ranges to populate the graph depending on what data type is selected.  This does eliminate the need for any conditional formatting. 

Thank you for your fast help!

Answer 5

check if this helps

http://office.microsoft.com/en-us/excel-help/edit-titles-or-data-labels-in-a-chart-HP001234167.aspx

Harrow