Chart not displaying Currency symbol in Axis and Data Labels

[MikeToon]

Hi Guys

I have a very frustrating problem and I hope someone can help me out here...

1. I have a table with various columns that display currency values.
2. The table displays the currency of a selected cell A1(Euro, Pound etc.)... I have been able to get the Table to change to the currency selected by using (Conditional Formatting/New Rule/"Use a Formula to Determine which Cells to Format") and this works very well ie. the cells in the table change currency depending on the value of Cell A1....

My problem is...

- When I create a Chart using the Table in question... the Axis and Data Labels do not display the currency symbol of the data as reflected in the Table....???

It worked fine when I simply had the cells formatted for US$'s (as an eg.).... but now... nada....

Any ideas??

Thanx

MikeT

 

[DILIPandey]

Hi MikeT,

I have been able to get the Table to change to the currency selected by using (Conditional Formatting/New Rule/"Use a Formula to Determine which Cells to Format") and this works very well ie. the cells in the table change currency depending on the value of Cell A1....

Are you changing the currency by conditional formatting option ?


Regards,
DILIPandey

 

[MikeToon]

Hi DILIPandey

Yes... the conditional formatting changes the currency symbols in the Table based on the value of Cell A1... and this works fine in the Table... but...

...the Chart based on the Table in question does not seem to pick these values up as a Currency... the Chart is reading them as a Number and hence is not displaying the currency symbol in the Chart Axis or Data Labels...?

If that makes any sense...?

Thanx

MikeT

 

[DILIPandey]

May be I am not getting the point here.. sorry.

Okay... copy any cell with currency symbol from your table and select an empty cell, do paste special values. What value do you see? Is that currency symbol still there ?

Also, can you post a snapshot of the table here ? Thanks.

Regards,
DILIPandey

 

[MikeToon]

Hi DILIPandey

Ok... I see where you are going with this....

I copied the currency cell into an empty cell and pasted it as value as requested.... The result was a $ value (which is the initial Formatting criteria of the Table), however, the actual cell formatting should be a £ symbol based on my conditional formatting ("Use a Formula to Determine which Cells to Format")...based on Cell A1

So ie. The Conditional formatting has changed the formatting of the Table (cells show £ values) ... but the "actual" cell formatting stays $'s .... This ($'s) is reflected in the Chart Axis and Data Labels... but it should be(£'s)... based on what the Table reflects... hehehhh

Ummmm... I'm not sure how to insert a screen shot... but it's a really complicated Table so I'm not sure how that will help you...

Anyway... thanx for your help... I hope I'm making sense...

MikeT

 

[MikeToon]

Sorry, just to clarify things... I changed the Cell Formatting from "Number" to "Currency $'s"..... as I realized that a "Number" would not reflect a currency symbol in the Chart Axis and Data Labels as initially mentioned in this post...

 

[MikeToon]

Ok... well.... you win some you loose some...

Correct me if I am wrong... but Conditional Formatting does not carry over the formatting of the original Cell Formatting...

In order for me to accomplish what I had in mind, I would need to create spreadsheets for each currency pair (cell formatted for each currency) and then use a comprehensive IF statement (in the Chart setup) based on Cell A1 when determining the Chart Range....

Maybe this would be an option for more simple Charts... but... I'm just going to slap on a Key/Legend that states the currency and just use "Numbers" as Chart Data Labels...

Anyways... I hope someone learnt from this post...

Happy coding...

MikeT
PS: Thanx DILIPandey... you steered me in the right direction... cheers

 

[DILIPandey]

You are welcome MikeT


Cheers

Regards,
DILIPandey

 

[nmounir]

I am experiencing the same issue as in I am using two different signs (currency and Percentage) and they do not show in the chart. They all show as as currency. Conditional formatting doesn't override the actual sign in the table. I am not sure how u solved it ?

 

[nmounir]

I do not want to change the orignal cell formatting because the table is dynamic