I need to switch currencies based on a pull-down selection

  • Thread starter Thread starter rband
  • Start date Start date
R

rband

How can I cause a range of cells to change from Dollar to either Pounds or
Euros based on the appropriate selection from an in-cell pulldown?
 
How can I cause a range of cells to change from Dollar to either Pounds or
Euros based on the appropriate selection from an in-cell pulldown?
Click to expand...

I would put the exchange rates in a cell somewhere, I assume you'd
have this already. And do one for dollars, too, with just a 1 in the
cell. Then name each of those rate cells with the same name as the
selections for your pulldown. Then have the range cells calculate
with:
=YourFormula*INDIRECT(B1)

Where B1 is your pulldown cell.

You'd also want to do Conditional Formats if you plan on having the
currency symbols formatted.
 
Spiky, thanks but I guess I didn't explain myself clearly. I understand what
you've suggested and actually I already had that in the spreadsheet. What I
am attempting to do is to take a column of numbers formatted as currency in
US dollars and whan I select pounds or euros, the calculation works fine I
just want the symbols to change to either pounds or euros. Thanks for the
help and sorry I wasn't clearer.
 
rband said:
Spiky, thanks but I guess I didn't explain myself clearly. I understand what
you've suggested and actually I already had that in the spreadsheet. What I
am attempting to do is to take a column of numbers formatted as currency in
US dollars and whan I select pounds or euros, the calculation works fine I
just want the symbols to change to either pounds or euros. Thanks for the
help and sorry I wasn't clearer.
Click to expand...

Some variation of the following could work:

=IF(B1="Pounds",TEXT(A1,"[$£-809]#,##0.00"),IF(B1="Euros",TEXT(A1,"[$€-2]
#,##0.00"),TEXT(A1,"$#,##0.00")))
 
Spiky, thanks but I guess I didn't explain myself clearly. I understand what
you've suggested and actually I already had that in the spreadsheet. What I
am attempting to do is to take a column of numbers formatted as currency in
US dollars and whan I select pounds or euros, the calculation works fine I
just want the symbols to change to either pounds or euros. Thanks for the
help and sorry I wasn't clearer.
Click to expand...

Crap, I forgot that conditional formatting doesn't include number/text
formatting. At least, not before xl2007. Bizarre oversight by
Microsoft.

I see 2 options. One is VBA to change the format since conditional
doesn't work. Two is to have a mess of IF/TEXT formulas to do this.
But then any SUM or whatever calc you do on this range will have to be
switched back to values to work.

So this changes the formatting to include the currency sign, but
changes to text format:
=IF(dropdown="dollar",TEXT(formula,"$#,##0.00_);($#,##0.00)"),
IF(dropdown="euro",TEXT(formula,"[$€-2] #,##0.00_);([$€-2]
#,##0.00)"),
TEXT(formula,"[$£-809]#,##0.00;-[$£-809]#,##0.00")))

I tried a simple SUM/VALUE array formula to see if I could add these
"text" numbers up. It works with dollars, works with euro, doesn't
work with pounds. Damn British.
 
Thanks, I'll play around and see what I can come up with.

Glenn said:
rband said:
Spiky, thanks but I guess I didn't explain myself clearly. I understand what
you've suggested and actually I already had that in the spreadsheet. What I
am attempting to do is to take a column of numbers formatted as currency in
US dollars and whan I select pounds or euros, the calculation works fine I
just want the symbols to change to either pounds or euros. Thanks for the
help and sorry I wasn't clearer.
Click to expand...

Some variation of the following could work:

=IF(B1="Pounds",TEXT(A1,"[$£-809]#,##0.00"),IF(B1="Euros",TEXT(A1,"[$€-2]
#,##0.00"),TEXT(A1,"$#,##0.00")))
Click to expand...
 
Thanks, I'm going to take what Glenn suggested and what you've presented and
see if I can make some magic!

Spiky said:
Spiky, thanks but I guess I didn't explain myself clearly. I understand what
you've suggested and actually I already had that in the spreadsheet. What I
am attempting to do is to take a column of numbers formatted as currency in
US dollars and whan I select pounds or euros, the calculation works fine I
just want the symbols to change to either pounds or euros. Thanks for the
help and sorry I wasn't clearer.
Click to expand...

Crap, I forgot that conditional formatting doesn't include number/text
formatting. At least, not before xl2007. Bizarre oversight by
Microsoft.

I see 2 options. One is VBA to change the format since conditional
doesn't work. Two is to have a mess of IF/TEXT formulas to do this.
But then any SUM or whatever calc you do on this range will have to be
switched back to values to work.

So this changes the formatting to include the currency sign, but
changes to text format:
=IF(dropdown="dollar",TEXT(formula,"$#,##0.00_);($#,##0.00)"),
IF(dropdown="euro",TEXT(formula,"[$€-2] #,##0.00_);([$€-2]
#,##0.00)"),
TEXT(formula,"[$£-809]#,##0.00;-[$£-809]#,##0.00")))

I tried a simple SUM/VALUE array formula to see if I could add these
"text" numbers up. It works with dollars, works with euro, doesn't
work with pounds. Damn British.
Click to expand...
 
Adjust to suit. DV dropdown assumed H1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("A1:F50")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "Pound"
.NumberFormat = "£#,##0.00"
Case "Euro"
.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
 
Gord - This is perfect! Thanks very much.

Gord Dibben said:
Adjust to suit. DV dropdown assumed H1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("A1:F50")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "Pound"
.NumberFormat = "£#,##0.00"
Case "Euro"
.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
Click to expand...
 
I have the same need with a variation: I want the currency format of cell I1
to be based on the entry in cell H1; format of cell I2 based on H2; etc. Is
this possible through this method?
 
Back
Top