Change currency formatting on value of a cell

[Closed]
Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello,
I have a conditional formatting issue in Excel 2007.

I have a drop down list which shows 4 countries; UK, DE, FR and NL

I would like to change the format of a number of cells to £ or € dependent on what the dropdown box has chosen.

I have a number of vlookups, so it pulls the right amount, and the right SKU, but I cannot get it to change the formatting correctly. I have even tried to simplify it to say, if UK use £ and if <> UK then use €, but that doesn't change either. If I change it, so it colours red for UK, and clear for not UK, that works.

Can anyone help please?
Thanks, 17ND

2 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Hi 17ND,

suppose your coutries in coulmn B and number in cell D then use conditional formating as

in Formula use =$B1="UK" in format select curreny £ and in applies to give range to column D,
and add new rule as
in Formula use =$B1="DE" in format select curreny € and in applies to give range to column D and so on.

Hi thumic,

remove one $ from your formula
=$A$1="SEK"
write it as
=$A1="SEK",

Regards,
Rahul
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Maybe we can work together on this... I'm in the same situation as you are. I've created a list of currencies using "Name Manger" and put them as a drop down selection in A1. Below that I got different values to be entered or auto calculated. I now want these values to "adopt" the currency chosen from the drop down above. I've used "Conditional Formatting" - "Manage Rules" and added, for each currency, the following rule /=$A$1="SEK"/ (without the / ) then in format I've set the currency to "SEK" without commas - hit OK. Click new rule and continue /=OR$A$1="Dollar"/ then in format I've set the currency to "$US" without commas and so ... for each currency I've got in there, I've created a rule in conditional formatting. Once you're done with all of them hit Apply. It works abosultely great when changing 1,2 times but if you change more often, it gets stuck on (in my case) the last currency in the list used in drop down, and when you select one that was earlier on the list it won't change back automatically. I've tried "Data" - "Refresh All" no luck... I've tried checking and unchecking "Stop if true" in the conditional formatting window for one, some and all of the rules - no luck.... Maybe my little workaround helps you in getting to a working solution and by that we've helped each other out... Best regards and good luck, Michael
I have the same problem. It turns out that it actually prints out correctly, it just doesn't change the formatting correctly on the screen. I seems like a genuine bug in Excel 2007