Currency symbol not importing with IF statement

[sainoz]

Hi,

I have a list of options to select from that will determine a currency and hourly rate to display in a separate tab. The rate amount is displaying but not the currency symbol

Example:

When these rates are used elsewhere these are not bringing across the currency just the rate value. Is there a way to import the currency with the rate value?

Thank you.

 

[etaf]

what formula are you using to bring over the value ?
perhaps you could use conditional formatting to also bring over the format of the cell , which i suspect is the issue as the Currency Symbol is not part of the value but the formatting - or am i wrong

 

[sainoz]

Hi @etaf - using the following formula:

=IF('Pricing'!$L$3="USD Rate",'Pricing'!$J$58,IF('Pricing'!$L$3="GBP Rate",'Pricing'!$J$59,IF('Pricing'!$L$3="Other",'Pricing'!$J$60,"Not Selected")))

Yes, the currency is just part of the formatting of the cell, how do I bring that across to? Thanks so much.

 

[etaf]

so in L3 you have the codes
so you could use that in a conditional formatting rule
as they are all absolute reference - $Column$row

in the cell with the formula
lets say its A1
Also you will need 2 rules

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A1

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=OR( pricing!$L$3="USD Rate",pricing!$L$3="other")

Format [Number, Font, Border, Fill] - choose the Number > Accounting OR Currency depending on how you want it to display, $ in this case
choose the format you would like to apply when the condition is true
OK >> OK

Repeat rule for
"GBP Rate" format as £

 

[sainoz]

Thanks so much will give this a go. Much appreciated.

 

[etaf]

you are welcome