+ Reply to Thread
Results 1 to 7 of 7

How to display multiple currencies in one cell

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    49

    How to display multiple currencies in one cell

    Good afternoon all.

    I have run into a problem with formatting a cell.

    To cut a long story short, I have been given a sheet with various columns (all numbers, however these are currency values). Each column has a header showing the currency. Column 1) Dollars, Column 2) Pounds etc.

    On a seperate tab, I have a tool which uses data validation drop downs. One of the drop downs dictates which column should be looked up for the currency value (above).

    I am trying to format the cell to actually show the currency. So if column 1 is referenced, I want it to show $100.00, if column 2 100.00.

    Is this possible without having to format the actual data source?

    Many thanks,

    Andy.

  2. #2
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: How to display multiple currencies in one cell

    Hi Andy,

    Based on the change in currency type in column E, the cells show the currency amount (without $ or symbol though).
    I've used INDEX and MATCH functions to achieve this.

    Refer attached workbook. Is this what you need?
    Attached Files Attached Files
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  3. #3
    Registered User
    Join Date
    10-10-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: How to display multiple currencies in one cell

    Hi Sarang,

    Thank you for you post. I will def use your layout for something else I am working on.

    However, in this situation I am basically looking for the cell to show 100.00 or $100.00 etc.

    My drop down has for example: USA. This means when I do my lookup, I am referencing the column with the Dollars, however I want this to be shown as $100.00 not 100.00. The same goes for when my drop down shows UK. 100.00 not 100.00.

    I dont know if this is possible, maybe VBA?

  4. #4
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: How to display multiple currencies in one cell

    Hi Andy,

    See if the contents of this link is of any help to you. There is also a sample file available for download which I've attached.

    Conditional Formatting for Currency Symbol
    Attached Files Attached Files
    Last edited by Saarang84; 06-12-2014 at 09:32 AM.

  5. #5
    Registered User
    Join Date
    10-10-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: How to display multiple currencies in one cell

    Hi Sarang,

    Thank you for your help!

    I have fudged it and just did created a if function next to the cell to indicate a or $. Alligned to the right so it looks like it is part of the cell!

    Its given me to much of a headache to keep trying! lol.

    Thank you for trying though!!!

  6. #6
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: How to display multiple currencies in one cell

    So, which solution would you use?? I want a 1000 reps though

  7. #7
    Registered User
    Join Date
    10-10-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: How to display multiple currencies in one cell

    I basically just created an IF function to reference one of the data validation drop down boxes. I could determine the currency from this drop down box. I embedded this formula next to the cell with the value. I then formatted the cell to place the currency symbol to the right which looks as if it is part of the cell.

    It works fine, but I know its not what I wanted to do so I may use your links to find a better option when I havent already been working on the file for 8 straight hours and I can think clearly.

    I have added rep (told me I had to spread it around before I gave you anymore!)

    Thanks for the help mate!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Currency Formatting for multiple currencies
    By rohithkotagiri in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-04-2018, 03:17 PM
  2. Multiple currencies in Excel
    By titaneu in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2014, 10:51 AM
  3. Multiple Currencies
    By Nikamorphosis in forum Excel General
    Replies: 5
    Last Post: 08-13-2013, 04:51 PM
  4. Adding Multiple Currencies
    By n00bie in forum Excel General
    Replies: 2
    Last Post: 10-08-2007, 10:53 AM
  5. How to handle multiple currencies with one spreadsheet
    By Michael Mullican in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 01:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1