• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to change the currency symbol dynamically in Excel

K

kintrachat

New Member
I have a price sheet that is all based on USD ($). I also have a list of foreign exchange rates in which the user can select when they go to the pull down menu and select the appropriate currencies.


Once they have selected the currencies, I can easily convert USD to the currency, however I don't know how to dynamically change the $ symbol to the appropriate currency symbol for the whole price sheet.


Is there an easy way? or a way to do this without having to edit all of the cells manually?


thanks!
 
just for an example...


lets say I have this


A B

Name Cost

Apples $1.00

Oranges $0.50

Veggies $5.00


the user now selects the currency in which they want to see the prices in, and they select the british pound.


After I do my currency conversion, how do I change the "$" to the GBP symbol?


Thanks
 
The easiest way is to use a Text() function to display your numbers

In the text function use a Choose() function as part of the format part

I'll post an example later
 
You can do a couple of things


1. Use a Formula like

=Text(C2, Choose(A2, "$", "£", "¥","€")&"0.00"))

or

=Text(C2, Choose(A2, A3, A4, A5)&"0.00"))

or

=Text(My Formula, Choose(A2, A3, A4, A5)&"0.00"))

where C2 or My Formula has the value /formula you want to display

A2 has the index number from your currency drop down

A3:A5 have various currency symbols


2. Use Conditional Formatting

You can add a CF to the worksheet or various areas where each CF will have a simple formula like =A2=1

Then setup a Custom Number format as appropriate

You will need to add as many CF as there are Currency choices
 
Thanks Hui, but i also need the numbers to stay numbers since i am also doing some other calculations, i.e. cost per month and cost per year.


Thanks Narayank991. i will test out some of those VBAs
 
I found an even easier way.


I created two columns:

FX Name FX Symbol


Listed out all of the names and symbols in a worksheet, named the range fx_rates


then when the user selects the currency they want to convert to, the user chooses the currency in the pull down menu, to convert all numbers to the appropriate currency.


then in a cell ("E3") in the FX rates worksheet, i do a vlookup to match the appropriate symbol,


then call the sub to apply the format to all of the cells that i want converted.


Sub Currency_Calculate()

Currency_Symbol = Worksheets("FX rates").Range("E3")


Worksheets("Calculator").Range("F11:N49").NumberFormat = Currency_Symbol & " #,##0.00"


End Sub


fast and simple and not a lot of coding. in addition, if i ever do any updates, i won't have to refer to the VBA code, but rather my named ranges.
 
Glad you found something that worked, and thanks for sharing your solution. =)
 
You must log in or register to reply here.
Back
Top