Format cell with currency informed in other cell using a single formula
Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,051
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default 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))
    Last edited by AlphaFrog; Aug 25th, 2019 at 06:28 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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))))))

    Sam_D_Ben

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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