Linked to source Data Labels converts £ to $

Rottimad

New Member
Joined
Mar 2, 2015
Messages
19
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

New Member
Joined
Mar 2, 2015
Messages
19
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

Board Regular
Joined
Sep 25, 2014
Messages
62
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

New Member
Joined
Mar 2, 2015
Messages
19
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.
 

Forum statistics

Threads
1,082,244
Messages
5,363,981
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top