• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying custom format based on changing values

D

DashboardNovice

Member
In the attached file, I am trying to figure out how to apply a custom format based on the currency that a user selects. I suppose this would involve a VLOOKUP.

In cell E3, the user would select a currency. Then the value they enter in E5 would be custom formatted based on the currency they selected.

I have a table showing the currency symbol to use depending on the currency selected.
 

Attachments

  • Chandoo - Apply Selected Currency Format.xlsx
    9 KB · Views: 8
Hi ,

Using VBA will make it simple , while using Conditional Formatting rules means adding as many rules as there are currencies in your table.

What is your preference ?

Narayan
 
NARAYANK991 said:
Hi ,

Using VBA will make it simple , while using Conditional Formatting rules means adding as many rules as there are currencies in your table.

What is your preference ?

Narayan
Click to expand...

I would like to understand how both work. But if I had to choose one, I guess I would say conditional formatting. My apologies for changing my mind again.
 
Last edited:
NARAYANK991 said:
Hi ,

This is the CF version ; I have put in the rules for only the first 3 currencies.

Narayan
Click to expand...

This is awesome. Thank you sir. I took a look at your CF rules and I understand the idea.

I forgot to mention something. Suppose I wanted to display a symbol before and after a value. For example EUR 1.1503/USD. The green cell has an example of what I am trying to get. I would like the value displayed to still be a number so calculations can be done on it.

I thought about using a CF rule like you had for the first symbol, then to append the "/USD" part. I thought about using Custom Cell Format, then somehow inserting a cell reference after the 0.00 in the "Type:" field where you define the custom formatting, but I don't think you can insert a cell reference to accommodate changing values.

I modified the values in columns I:K for this example. File attached. I removed your previous CF rules in order to start from scratch.

Do you think this is even possible?
 

Attachments

  • Custom Formatting with Currency Symbols.xlsx
    9.3 KB · Views: 4
NARAYANK991 said:
Hi ,

Something like this ?

Narayan
Click to expand...


Yes. That's just what I was looking for. I have two questions:

1. I don't understand how you got the /USD or /JPY portion of the formatting to appear. How did you do that?

2. In your CF formula, you have a space character before and after the 2nd equal sign. What is the purpose of having those spaces? Is this necessary?
 
Hi ,

Cf has two parts to it , a CF rule defined using a formula , and a format which will be applied when the formula evaluates to TRUE.

If you see the Custom Format which is applied within the CF , you can see that the /USD , /JPY portions are just text which are added to the numeric formatting.

The additional spaces , which are within the format string , can be removed if you don't want them.

Just click on the Format button within CF and you can modify the format string.

Narayan
 
NARAYANK991 said:
Hi ,

Cf has two parts to it , a CF rule defined using a formula , and a format which will be applied when the formula evaluates to TRUE.

If you see the Custom Format which is applied within the CF , you can see that the /USD , /JPY portions are just text which are added to the numeric formatting.

The additional spaces , which are within the format string , can be removed if you don't want them.

Just click on the Format button within CF and you can modify the format string.

Narayan
Click to expand...

Ok. Now I see it. I was looking at the custom format that appeared by default in the "Type" field, instead of looking in the list.

Just one more question. In that custom format you have [ ] around the first currency. What is the purpose of the [ ], or are those option?
 
Hi ,

These are automatically inserted by Excel , when you select the country and the symbol from within the Format , Currency dialog.

If you manually enter the $ symbol , by enclosing it in quotes , this will not happen , but if you select the US and the $ symbol from within the dialog , then Excel will insert it.

Thus , the following two format codes are practically the same , at least in the English version of Excel ; the language may make a difference , someone can confirm / clarify.

[$EUR] #,##0.00

"EUR" #,##0.00

Narayan
 
NARAYANK991 said:
Hi ,

These are automatically inserted by Excel , when you select the country and the symbol from within the Format , Currency dialog.

If you manually enter the $ symbol , by enclosing it in quotes , this will not happen , but if you select the US and the $ symbol from within the dialog , then Excel will insert it.

Thus , the following two format codes are practically the same , at least in the English version of Excel ; the language may make a difference , someone can confirm / clarify.

[$EUR] #,##0.00

"EUR" #,##0.00

Narayan
Click to expand...

This makes sense. Thank you sir.
 
You must log in or register to reply here.
Back
Top