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

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" wrote:

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,

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

"Mike H" wrote:

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" wrote:

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

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

--
Gary''s Student - gsnu200804


"smaruzzi" wrote:

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

"Mike H" wrote:

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" wrote:

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

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:

£10

--


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

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200804


"smaruzzi" wrote:

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

"Mike H" wrote:

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" wrote:

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

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

On Sat, 13 Sep 2008 09:05:01 -0700, smaruzzi
wrote:

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200804


"smaruzzi" wrote:

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

"Mike H" wrote:

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" wrote:

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