Ask Your Question
1

format number as currency based on next cell content

asked 2017-12-25 09:59:30 +0100

Mikele gravatar image

updated 2017-12-25 10:00:07 +0100

Hello,

I have a table with two columns: Column A = numbers Column B = currency codes (i.e. USD, JPY, EUR, etc...)

example: https://drive.google.com/open?id=1tvm... ^ (screenshot)

how can I have cells on column A to automatically format as the correct currency based on the code column B on the same row?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-12-27 06:50:26 +0100

Jim K gravatar image

updated 2017-12-27 06:51:20 +0100

Copy format codes from Format -> Cells, Number, Currency into a lookup table.

D         E
~~~~~~~~  ~~~~~~
currency  format
USD       [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00
EUR       #,##0.00 [$€-612];[RED]-#,##0.00 [$€-612]

Now use the TEXT function and look up the format. Enter the following formula for A2.

=TEXT(250000;VLOOKUP(B2;D$2:E$3;2))

The result:

formatted currency values

edit flag offensive delete link more

Comments

Thank you Jim, very interesting, I didn't know about the TEXT function; I'll give it a try!

Mikele gravatar imageMikele ( 2017-12-27 08:59:43 +0100 )edit
1

answered 2017-12-27 08:58:04 +0100

Mikele gravatar image

updated 2017-12-27 09:00:01 +0100

Meanwhile, it seems like I have succeeded in using conditional formatting, like this:

https://drive.google.com/open?id=1x0x...

(to see the conditions applied to the data in your screenshot, replace Q52 and R52 with A2:A500 and B2 respectively ... I had to create a custom style for each currency I am testing against)

It took me a long while to figure it out, because I was missing to first clear all existing formatting for the cells in column Q (column with numbers in my table)

The table I am building is a work-in-progress, I haven't had the chance to 100% verify this approach, but at first glance it seems to work.

edit flag offensive delete link more

Comments

This is a good solution as well. I upvoted so that you have 20 karma points, which is enough to accept your own answer if that's what you decide.

Jim K gravatar imageJim K ( 2017-12-27 12:09:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-25 09:59:30 +0100

Seen: 148 times

Last updated: Dec 27 '17