Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    conditional formatting to display a currency symbol based on text

    Hi,

    I would like to use conditional formatting to format a numeric cell based on the text in the cell next to it on a line by line basis.

    i.e.

    A B
    USD 1000
    EUR 1000
    GBP 1000

    I want B2 to update to $1000 and B3 1000 etc, this could be over 200 lines but need to do this without code.

    Any ideas?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I couldn't do this using Conditional Formatting (Excel 2003), but does the attached give you some ideas ?
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    HELP fellow loungers !

    My little example, Currency.xls attached above, works fine except when you want to use the resulting formatted cell in a calculation.

    When the Currency is GBP or EUR all is well, but when it is USD it throws up a #VALUE! error - and I cannot see why !

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Ok, workaround attached.

    But I'd still like to know why the first version didn't work, although only when the format was $0.00 ?!
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi stimpsond1

    You could also use Conditional Formatting, select column B or range of cells in column B you wish to apply the rules.

    For USD: =$A1="USD" format as US dollars.
    For GBP : =$A1="GBP" format as GB pounds
    Etc
    Last edited by Kevin@Radstock; 2013-02-04 at 12:07. Reason: Going mad!

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Kevin,

    I don't think that you can set Conditional Formatting to use a currency format ?

    Maybe its me that's going mad !

  7. #7
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi Martin

    In Excel 2007 & 2010 you can. I have noticed that you uploaded xls file, not possible in 2003!

    Kevin

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Kevin.

    For me, that small advantage doesn't compensate for having to cope with ribbons !

    M

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..for 2007 and 2010 you can get an add-on that gives you the old Menus in the ribbon.
    Best of all worlds.

    zeddy

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Hmmm - I think I am comfortable where I am. Everything works, and I know how to get the best out of it. I'd rather spend my energy on doing new things with the environment I have than learning a new one.

Posting Permissions

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