Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2719
  • Last Modified:

Excel, formatting currency with a dollar or euro symbol depending on condition

I have a budget spreadsheet that is currently formatted for dollars.  

I set the cells with rates that we use to check a field.  If that field says "US" it uses the base rate which is in US dollars if it says "EURO" it multiplies it by the conversion rate for euros.

Each cell is formatted for US Dollars with the US dollar symbol displayed, but if I set that field to "EURO" I want it to apply the rate and also display the euro symbol instead of the dollar symbol.  Any idea on how to do this?
0
nycbrian80
Asked:
nycbrian80
  • 2
1 Solution
 
Harisha M GCommented:
Hi, AFAIK, you can only change the global settings in control panel.

However, you can make Excel to "display" what you want..

Assuming you have the EURO|US cell as say, C1 then.. you can have this code in the sheet module:


1. Press Alt + F11 to goto VBA.
2. DoubleClick the Sheet where you want the behaviour
3. Paste the code given...
4. Press Alt + F11 again to come back to Excel.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, [C:C]) Is Nothing) Then Exit Sub
   
    For Each cell In Intersect(Target, [C:C])
        Cells(cell.Row, 1).NumberFormat = IIf(cell = "US", """$""", """€""") & " #.00"
    Next
End Sub

Ex: http://mgharish.5gigs.com/ee/DollarEuro.xls


---
Harish
0
 
nycbrian80Author Commented:
Okay this works, but how would I modify to for example only look at one specific cell where I specific dollars or euros.  Depending what I put in that cell it changes all the values within a range.  

For my example I want specify US or Euro in cell L1

and I want it to effect the range E9:F144
0
 
shy_talkCommented:
The above looks just fine, but for those allergic to code, and if you only need the figure for display purposes there may also be an alternative.

Not entirely sure what you want, but the principles of this should still apply (If you want to calculate using the amounts, then you will probably need an extra (perhaps hidden) column with the unformatted amounts).

Drop these into A1 of your sheet

Amount      Currency      Converted      
3.5      US      $6.30      
3.5      Euro      €4.90      
Currency      Rate      Format      
US      1.8      [$$-409]#,##0.00      
Euro      1.4      [$€-1809]#,##0.00      

Replace the $6.30 in the top Amount with the formula =IF(B2="US",TEXT(B$5 *A2,C$5),TEXT(B$6*A2,C$6))

Select the cell you have just dropped the formula into and
drag the formula box down over the €4.90 using the formula drag box at the bottom right corner of the cell.

Try changing the formula from Euro to US and back again,

The TEXT function converts numbers to text using whatever format you want.

The formats can be cut from the Custom Format text box once having set a particular currency format in Format|Cells option for the cell, so use whatever format you please and substitute those for the two I have used.

You can, of course also hide or protect the format column if you wish (I don't particularly advise having the formats and the rest of the look up table in the same columns as the data. That's just for ease of demonstration.

Hope some of this helps. Good luck anyway!
0
 
Harisha M GCommented:
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, [L1]) Is Nothing) Then Exit Sub
   
    Range("E9:F144").NumberFormat = IIf([L1] = "US", """$""", """€""") & " #.00"
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now