Country / Currency conversion

[2022]

Hi, I need to make the currency in a cell dependent on the country a user has selected.

It's possible to do that using an IF statement, with a handful of countries. However, there's a limit on the number conditions an IF statement can take.

Is there a way to do this, for all countries, using an alternative method or even using VBA?

For context, let's say that cell C2 was a data-validated cell that was based on a list of cells in H2:H100, and H2:H100 had a list of countries.

So if H2 had "USA" in it,
and H3 had "France" in it,

a user would be able to select "USA" from the drop-down in cell C2, and then the US Dollar (in Excel format) would appear in cell D2.


If they selected "France" in cell C2, then the Euro (in Excel format) would appear.

Has anyone done this before?

Thanks in advance.

 

[DRSteele]

You could make dozens of Conditional Formatting rules. It wouldn't be as many as you'd think since many countries use a dollar sign and many use a Euro, etc.

 

[2022]

Thank you for your response.

Yes, I'd seen the conditonal formatting option.

But I was wondering if there's a quicker way - I have nearly 200 countries to create rules for!

 

[Joe4]

ut I was wondering if there's a quicker way - I have nearly 200 countries to create rules for!

But as Don alluded to, you won't actually need to create 200 rules.
So, all the countries that use the "$" could probably be one rule.
All all the countries that use the Euro sign could use another rule.
etc.

 

[Eric W]

It sounds like you may be looking for a lookup table:

Book1
	A	B	C	D	E	F	G	H
1			Country	Currency			Country	Currency
2			France	Euro			USA	USD
3							England	Pound
4							France	Euro
5							Japan	Yen
6							Italy	Euro
7
Sheet1

Cell Formulas
Range	Formula
D2	D2	=VLOOKUP(C2,G2:H10,2,0)

 

[Joe4]

Eric,

I could be totally wrong, but I think that Don and I were under the impression that they wanted to apply a Currency Format to the cell, and not return a text value.
That being said, my thought was to still use a VLOOKUP table to list all the countries and currencies, and then create a Conditional Formatting Rule that does the VLOOKUP and formats based on the value in the Currency column (i.e. if Dollar format with "$", etc).

 

[Eric W]

Eric,

I could be totally wrong, but I think that Don and I were under the impression that they wanted to apply a Currency Format to the cell, and not return a text value.
That being said, my thought was to still use a VLOOKUP table to list all the countries and currencies, and then create a Conditional Formatting Rule that does the VLOOKUP and formats based on the value in the Currency column (i.e. if Dollar format with "$", etc).

You may be right about the format, but I wasn't positive so I gave the other option. Using a table to choose the format would work well. I was also thinking we could add another column to hold the conversion factor, so if they choose 2 countries and an amount, it could do the conversion too.

 

[2022]

Eric,

I could be totally wrong, but I think that Don and I were under the impression that they wanted to apply a Currency Format to the cell, and not return a text value.
That being said, my thought was to still use a VLOOKUP table to list all the countries and currencies, and then create a Conditional Formatting Rule that does the VLOOKUP and formats based on the value in the Currency column (i.e. if Dollar format with "$", etc).

You were right in assuming that I wanted to format the cells to the currency chosen.

Eric's suggested solution is good, too. I'll look into both. Thank you.