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?

On Sep 3, 10:16 am, rband wrote:
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?

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.

"Spiky" wrote:

On Sep 3, 10:16 am, rband wrote:
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?

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.

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

On Sep 3, 12:51 pm, rband wrote:
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" wrote:
On Sep 3, 10:16 am, rband wrote:
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?

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.

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

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

Thanks, I'm going to take what Glenn suggested and what you've presented and
see if I can make some magic!

"Spiky" wrote:

On Sep 3, 12:51 pm, rband wrote:
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" wrote:
On Sep 3, 10:16 am, rband wrote:
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?

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.

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.

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

On Wed, 3 Sep 2008 08:16:01 -0700, rband
wrote:

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?

Gord - This is perfect! Thanks very much.

"Gord Dibben" wrote:

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

On Wed, 3 Sep 2008 08:16:01 -0700, rband
wrote:

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?

Good to hear

Thanks for the feedback.

On Thu, 4 Sep 2008 05:32:04 -0700, rband
wrote:

Gord - This is perfect! Thanks very much.

"Gord Dibben" wrote:

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

On Wed, 3 Sep 2008 08:16:01 -0700, rband
wrote:

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?

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?

"rband" wrote:

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?