Format cell with currency informed in other cell using a single formula

Tahitipit

New Member
Joined
Feb 3, 2015
Messages
5
HI,

I cell B1 I have a drop down list to select the currencies that are written in the ISO currency code (EUR, USD,....)
cell C1 I have the qty
Cell D1 the unit value
Cell E1 is C1*D1

I am looking for a formula to format the cell E1 as currency using the currency informed in B1

I have found solutions with conditional formating, with concatenation and also with macro , but cannot figure out a formula that would use B1 as argument to set the cell format as currency and using the currency in B1

Any idea ?

Thanks
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,267
Have a lookup table (F1:G3 in this example) that you can define the currency formats for each ISO code. Then use a VLOOKUP within a TEXT function to format the value.

FG
US$#,##0.00
EU#,##0.00 €
UK£#,##0.00

<tbody>
</tbody>

=TEXT(C1*D1,VLOOKUP(B1,F1:G3,2,0))
 
Last edited:

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
379
Another way is,

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(102,0,0)"><colgroup><col width="25px" style="background-color: rgb(255,255,255)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(255,255,255);text-align: center;color: rgb(102,0,0)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(102,0,0);text-align: center;">1</td><td style="text-align: center;;">C-List</td><td style="text-align: center;;">Quantity</td><td style="text-align: center;;">Unit Value</td><td style="text-align: center;;">Net</td><td style="text-align: center;;"></td><td style="text-align: center;;">Validation</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">2</td><td style="text-align: center;;">USD</td><td style="text-align: center;;">2</td><td style="text-align: center;;">12.5</td><td style="text-align: center;;">25.00$</td><td style="text-align: center;;"></td><td style="text-align: center;;">USD</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">EUR</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">GBP</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">YEN</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(102,0,0);border-top:none;text-align: center;background-color: rgb(255,255,255);color: rgb(102,0,0)">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(102,0,0)"><thead><tr style=" background-color: rgb(255,255,255);color: rgb(102,0,0)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">D2</th><td style="text-align:left">=TEXT(<font color="Blue">SUM(<font color="Red">B2*C2</font>),"#,##0.00"&IF(<font color="Red">A2="USD",CHAR(<font color="Green">36</font>),IF(<font color="Green">A2="EUR",CHAR(<font color="Purple">128</font>),IF(<font color="Purple">A2="GBP",CHAR(<font color="Teal">163</font>),IF(<font color="Teal">A2="YEN",CHAR(<font color="#FF00FF">165</font>)</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,102,325
Messages
5,486,165
Members
407,536
Latest member
farrukhikram

This Week's Hot Topics

Top