Conditional Formatting for Currency

Luke Brown

I currently am working on a project that has currencies from several different countries and ideally I would like for each cost to show the correct currency symbol type without having to manually change each cell. Since most of the users are working from reports, even if they change the currency type in the reporat as soon as they save and refresh or reopen the default currency from the sheet the reports are generated from will push back and overwrite their currency type. Current conditional formatting doesn't allow for currency type changes as far as I can tell.

I played with the idea of a form with a logic field that required selecting the correct currency type and based on the selection, then opening a field that went to a specific column with that was formatted to that currency type. Then using a main column with a if statement that would pull the value from correct column based upon currency type selection. Essentially columns doing a bunch of work in the background that most folks wouldn't see, but returning the correct information to a single column for everyone, but again default formatting for currency in the main sheet overrides this effort.

So my last solution would be to use a drop down box for currency type selection, and several columns that were populated just with currency type symbol. Then using an if statement that concatenated the currency symbol with a value entry that does not have a currency format applied. So something like, =if[Column1]="US Dollar",[Column3]@row+[Column2]@row, [Column4]@row+[Column2]@row. Where Column 2 would be the value and Column 3 and Column 4 would be different currency symbols. However I'm stuck finding a way to get some international currency symbols as just text.

I don't need a currency conversion as I've already built that, I just need the information in a single cost column to show up with the correct currency symbol in front of it.

Bassam Khalil

Hi @Luke Brown

Hope you are fine, for sure when you define the formatting of the main column as currency type and you select the $ for example as a default currency any number inserted in this column will take the format ( $ 2,000.00 ).

you can solve that with helper columns:

1- column1 for currency type.

2- column 2 for currency amount

3- Currency = =[currency type]@row + " " + [currency amount]@row

the following screenshot show the result:

Luke Brown

Bassam,

That's exactly what my plan was, but apparently I'm not smart enough to figure out how to get the currency type characters to show up as you've shown. Is this a font type, column type...?