Is it possible to create a custom format that uses a formula/named range

[lzatony]

Okay. I doubt this is possible other than by using VBA but I thought I would ask.


I would like to have a number cell's currency format change based on another cell. That is, a user selects a drop down with a value (ex: USD, EUR, KRW, etc) and the number's format automatically changes to a looked up format for the selected currency ([$$-409]#,##0, [$€-2] #,##0, or [$₩-412]#,##0).


I know how to do this through code, I was just wondering if I could somehow set it to a named range that would be a lookup to the format based on the three char code (And yes, I know I could create a separate column for the currency symbol but where's the fun in that?).

 

[Hui]

Lzatony


Firstly, Welcome to the Chandoo.org Forums


You can use the Text() function to do this

=TEXT(A1,A2)

A1 = value

A2 = Number format

or

=Text(Value, Format)

where

Value is a Named Formula/Range

Text is a Named Formula/Range

 

[inddon]

Hello Hui,

I would like to do it via a conditional formatting for multiple records. If it is possible, could you please guide?

I have attached a excel file. There are 2 worksheets:
1. 'Entry'. which has 2 columns:
a. Currency (drop down based on named range 'Curr'
b. Amount (Based on the above 3 letter, its equivalent symbol be displayed along with the amount
2. 'Currency Symbols' which has 2 columns
a. Currency Code (Named Range 'Curr')
b. Symbol (Named Range ' CurrSymbol')


Looking forward to hearing from you


Thanks & regards
Don