Assigning a currency symbol on the fly

[smaruzzi]

A track several financial data re to different European markets, hence with
multiple currencies. I would like to display numbers with the appropriate
currency, a information stored in cell A1.

Is there a smart way to dynamically display number preceded by the
corresponding currency symbol so that each figure is clearly understandable?
I have found a solution thru conditional formatting, but not particularly
efficient.

Thanks, Stefano

 

[Mike H]

Hi,

How would we/Excel recognise the diferent currencies if there is no
identifier?

10.23
10.23
10.23

One is dollars one is Euros and the other pounds!! I would be interested in
how you did it with CF

Mike

 

[smaruzzi]

Mike,

A1 contains a financial number: 10.00
B1 the country code: FR for France.

Then with the cursor on A1 I selected Conditional Formatting | New Rule |
Use a formula to select ... (the last one in the list).

The formula is the following: =$B$1="FR"
And then after pressing the Format button i selected Currency and the Euro
symbol.
I repeated the same sequence several times, one per currency.

Not easy to maintain and extend over time, but it works.

Stefano

 

[Gary''s Student]

First in B1 th C100 enter a country - currency table:

fr €
gb £
us $

and in A1 something like:

=VLOOKUP(A2,B1:C10,2,FALSE) & 10

Now all we have to do is enter the country code in A2 and A1 will have the
correct symbol in front. If A2 contains gb, then A1 displays:

£10

 

[James Silverton]

Gary''s wrote on Sat, 13 Sep 2008 07:21:00 -0700:

fr €
gb £
us $

and in A1 something like:

=VLOOKUP(A2,B1:C10,2,FALSE) & 10

Now all we have to do is enter the country code in A2 and A1
will have the correct symbol in front. If A2 contains gb,
then A1 displays:

Admittedly, it's a bit redundant but why not use the standard
abbreviations: EUR, GBP, USD etc? What's an "fr" anyway? You could also
probably autocorrect them tho I don't know if that works in a numerical
format.
Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[smaruzzi]

Gary,

thanks but that doesn't help me. Your solution would mean entering all
number in the spreadsheet as part of a formula, which is not practical at all.

Thanks Stefano

 

[Gord Dibben]

Are you looking to change all numbers in a range to the same currency symbol
based upon the country in A1?

Could be done using a DV dropdown list in A1 and some event code.

Similar to this which could be tailored to your needs.

Adjust to suit. DV dropdown assumed A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("A2:F50")
Select Case Target.Value
Case "US", "CDN"
.NumberFormat = "$#,##0.00"
Case "GBR"
.NumberFormat = "£#,##0.00"
Case "FR", "NE", "PO", "IT"
.NumberFormat = "€#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that module, edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

 

[smaruzzi]

Gord,

fantastic. Learnt something new and super useful. Thanks a lot.

Stefano

 

[Gord Dibben]

Good to hear.

Thanks for the feedback

Gord,

fantastic. Learnt something new and super useful. Thanks a lot.

Stefano

 

[smaruzzi]

Gord,

one clarification. In my spreadsheet besides financial numbers I have % as
well. The code you supplied selects the range A2:F50. Is there a way to apply
the format exclusively to certain cells?

Thanks, Stefano

 

[Gord Dibben]

Many ways to do that.

One is to CTRL + select the cells to act upon and Insert>Name>Define

Give it a name like thelist

Change the code to.................With Me.Range("thelist")

Or change to With Me.Range("A2:C6,F1,G3,B11,G12")

Or just a couple of columns

With Me.Range("B:B,E:E")


Gord

 

[smaruzzi]

Super.

Thanks again, Stefano

Many ways to do that.

One is to CTRL + select the cells to act upon and Insert>Name>Define

Give it a name like thelist

Change the code to.................With Me.Range("thelist")

Or change to With Me.Range("A2:C6,F1,G3,B11,G12")

Or just a couple of columns

With Me.Range("B:B,E:E")


Gord

 

[smaruzzi]

Gord,

one more question: what if I wanted to multiply each financial number by a
constant (the exchange rate) so that I can dynamically display values in a
currency different from the original one by changing the currency symbol (you
taught me how to do it) and adjusting the values according to the exchange
rate?

Besides passing to the function another param - the exchange rate - how can
I force each individual value in the range subject to the currency symbol
formatting to be multiplied by the coefficient transferred to the function?

Thanks, Stefano

 

[Gord Dibben]

First of all you would have to store the original numbers somehow in an
array and operate on those with your exchange rates.

I'm not sure how to do that...............make that "I don't know how"

Or you could have a duplicate range, say on another sheet with the original
numbers and use those as the base for the currency formatting/exchange rate
calculations on the sheet we've been working on.

I don't know how your original numbers are derived...........formulas, data
import or??

Then you would need many more Cases to select from.

i.e. your could not use Case "FR", "NE", "PO", "IT" because each would
have its own exchange rate so would have be its own unique Case


Gord