Conditional Formatting using IF Statement Not Working

[Jennsy]

Hi,

I am using conditional formatting in a cell to show the currency symbol based on the currency selected. I have uploaded an example of this sheet.

In the ProjectCostBuilder sheet you can select the currency you want, then in the ProjectCosts tabs you can see a breakdown of the costs. I want the values in cells E7:E16 to have the currency symbol in front of the number. This works the first time you select the currency from the ProjectCostBuilder sheet, and sometimes the second or third time, but then it just stops updating and will be stuck on a currency no matter how many times you change the value in the ProjectCostBuilder sheet.

Book1ExcelForum.xlsx

Any ideas how I can resolve this?

Thanks
Jennsy

[etaf]

i have tried it quite a few times and it changes each time OK

is there anything specific you do that stops it updating ?

[Jennsy]

Hi Wayne,

It is a little bit random but generally what happens is this. I open the spreadsheet and it is set to EUR, and when I look at ProjectCosts it shows the prices in €. I then change the currency in the ProjectCostBuilder to Swiss Franc, and in ProjectCosts cell E6 will update to show the correct currency, but the values under that in cells E7 down don't update to show the correct symbol. I think it tends to be that when you move through the currencies from USD, to GBP etc it works until you get to Swiss Franc and then change it back to something else. Weird.

The conditional formatting formula I am using is for example:

=$E$6="USD" and the custom format for each currency is set.

Thanks,
Steve

[Jennsy]

Sorry, duped reply.

[etaf]

tried changing through all the currencies and back a dozen times or more and it all works OK for me

why do you have the separate rates sheet - you could just use a single row , rather than a whole table of different numbers - all the same

[Jennsy]

Hi Wayne,

Thanks for looking, must be something on my machine.

I have a separate rate sheet as there are a number of staff that could and do have different rates based on who they are, and I need to set rates for each currency (that aren't necessarily based on the current exchange rate). I don't necessarily need to have the currencies stored in the Rates sheet - was just doing that to try and keep it easy and tidy?!

Cheers,
Jennsy