Solved

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

Posted on 2006-06-22
4
2,689 Views
Last Modified: 2012-07-30
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
Comment
Question by:nycbrian80
  • 2
4 Comments
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16962674
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
 

Author Comment

by:nycbrian80
ID: 16963579
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
 
LVL 2

Expert Comment

by:shy_talk
ID: 16963611
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
 
LVL 37

Accepted Solution

by:
Harisha M G earned 125 total points
ID: 16963614
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now