Dynamically change how values are displayed in a chart?

Is it possible to dynamically change how values are displayed in charts?

I have a list which references different named ranges. It either needs to display the values in a graph as a number (without decimals) or a percentage depending on the source. Is this possible? Maybe with some VBA? Or am I asking too much from excel?

Doesn't Excel already do this? If your source is formatted correctly, it will be aware of the format required...

Yes it does, but not how I got my sheet set up. The cell values for the chart are filled by an hlookup, which can change depending on the selection of a dropdown menu.

So my question should be. Can de cell formatting change based on the source of the hlookup?
The general setting adds decimals and leaves out the thousand separator.

It can be done, with something along the lines of

Range("graphdata").NumberFormat = "0.00%"

but it's difficult to give exact code without seeing your workbook.

I can't share the workbook. But i do have an image of the workbook.
longterm.PNG

The formula in U8; =IF(HLOOKUP(U$4;INDIRECT("FC_"&STRM);$E8;FALSE)="";#N/A;HLOOKUP(U$4;INDIRECT("FC_"&STRM);$E8;FALSE))
STRM is a dropdown list in F1, containing different work streams.
E8 contains =VLOOKUP(C7;LIST_FIELD_INDEX;2;FALSE) which references to an index table. This changes based on the selection made in C7 (drop down menu)

The graph is based on data in row 8 and 9 which can be a number or a percentage.

Does that help?