Solved

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

Posted on 2006-06-22
4
2,700 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
Invest in your employees with these five simple steps to improve employee engagement and retention.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 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