3 letter currency code to format cell

[JackDanIce]

Hi,

I've got the following table of currencies and cell format codes:

PCT.xlsm
	H	I	J	K	L
1	Currency	Input	With cell formatting	Cell format
2	AUD	1	$1.00	[$$-en-AU]#,##0.00
3	CAD	1	$1.00	[$$-en-CA]#,##0.00
4	EUR	1	€1.00	[$€-x-euro2]#,##0.00
5	JPY	1	¥1.00	[$¥-ja-JP]#,##0.00
6	GBP	1	£1.00	[$£-en-GB]#,##0.00
7	NZD	1	$1.00	[$$-en-NZ]#,##0.00
8	USD	1	$1.00	[$$-en-US]#,##0.00
Position Size Calculator

Everything above manually generated.

I wish to create a drop down list for the currencys and have the related format affect the green cell (look like formatting in blue column)

PCT.xlsm
	H	I	J
10	Select Currency		Input value
11
Position Size Calculator

How can this be achieved and if other currencies are added to the list, the correct cell formatting is applied? Later, the table above will be an Excel table to allow for dynamic rows

TIA,
Jack

 

[jasonb75]

You would need to use conditional formatting with a rule for each currency.

Given that most $ currencies use the same format, you could probably use 1 rule for all rather than one for each country.

 

[JackDanIce]

Not a huge fan of CFs but you've inspired an idea...

Initially, created that table as part of posting this question but I can use it as a lookup table in via worksheet change event I'm already using.

Adding new currencies will be so infrequent that getting its related cell format code can be updated manually.

Not solved the way I initially wanted, but definitely resolves my issue! Thank you for the inspiration @jasonb75