Thread: Format cell with currency informed in other cell using a single formula Thanks: 0 Likes: 0

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

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

2. Re: Format cell with currency informed in other cell using a single formula

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.

 F G US \$#,##0.00 EU #,##0.00 € UK £#,##0.00

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

3. Re: Format cell with currency informed in other cell using a single formula

Another way is,

Excel 2013/2016
ABCDEF
1C-ListQuantityUnit ValueNetValidation
2USD212.525.00\$USD
3EUR
4GBP
5YEN

Sheet11

Worksheet Formulas
CellFormula
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))))))

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•