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.

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?

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!!!

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!