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?
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?
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.
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.
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.
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 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.
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.
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
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.