Linked to source Data Labels converts £ to $

[Rottimad]

Hi folks,

I'm pulling my hair out with what should be a simple problem and I don't have much left as it is.

I've created a bar graph in excel which displays different coloured bars depending on whether the value is positive or negative (having used some of the excellent advice many of you have already discussed in this forum). The data labels should display the value in £'s but for some reason my negative series values convert to $.

The source data is formatted the same across each series as:
£#,##0;[Red](£#,##0);_-* "-"??_-;_-@_-
and data labels are set to Linked to source (different worksheet, same workbook). If I change the format on all cells to something generic like £#,##0.00 I still get the same issue so I know its not my custom format.

As I said, this is only affecting the negative series not the positive series, even though they occupy adjacent cells. Ideally I don't want to take out the link to source. Occasionally I can enforce my will upon it and through various repeated attempts it does change to the correct £ symbol. But on saving and closing hey-presto here comes the $.

Anyone else come across how to rectify this??

 

[Rottimad]

Apologies, it would appear my Positive values are in fact NOT keeping their formatting. I must have unchecked the Linked to source at some point.

They now encounter the same issue as the Negative values.

I've found that the only way to enforce the currency to stick as £ is to set the formatting to Accounting.
_-£* #,##0.00_-;-£* #,##0.00_-;_-£* "-"??_-;_-@_-

This doesn't remove values which are blank and besides which, it isn't what I want it to look like! Call me picky.

 

[eb101]

If you are running into issues with the data connection, try to call the cell values into a different sheet and custom set those fields. I ran into issues when I data connected and fields I was using were being converted into text. I had to take for example A1*1 and reformat to Number to avoid direct text import. Hopefully that works for you.

 

[Rottimad]

If you are running into issues with the data connection, try to call the cell values into a different sheet and custom set those fields.

Eb101 - Thanks for your reply but I'm not using data connection. I think you may have misinterpreted my statement of "Source data". All information is currently contained within one book with no input/connection to any other workbook.

Solution?

I think I may have found a solution to my problem but don't know if this is a permanent fix, time will tell.

Instead of using a custom format as I did above with the generic "£" symbol I instead used "[$£-809]" after coming across it within the Symbol property setting. Both graphs now maintain the correct formatting even after closing.