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

[Tahitipit]

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

 

[AlphaFrog]

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.

[TABLE="width: 148"]
<tbody>[TR]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: left"]US[/TD]
[TD="align: left"]$#,##0.00[/TD]
[/TR]
[TR]
[TD="align: left"]EU[/TD]
[TD="align: left"]#,##0.00 €[/TD]
[/TR]
[TR]
[TD="align: left"]UK[/TD]
[TD="align: left"]£#,##0.00[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Sam_D_Ben]

Another way is,

Excel 2013/2016
	A	B	C	D	E	F
1	C-List	Quantity	Unit Value	Net		Validation
2	USD	2	12.5	25.00$		USD
3						EUR
4						GBP
5						YEN
Sheet11

Cell Formulas
Range	Formula
D2	=TEXT(SUM(B2*C2),"#,##0.00"&IF(A2="USD",CHAR(36),IF(A2="EUR",CHAR(128),IF(A2="GBP",CHAR(163),IF(A2="YEN",CHAR(165))))))