Using validation list to control currency cell formatting

[Nyanko]

I'm trying to find a way to influence the formatting of a cell based on a the value of another cell.

Let me explain

In cell A1 I have a drop down list of currency names : GBP, USD, EUR etc.....

Is there a way to use this selection to control the formatting of amounts entered elsewhere on the sheet ? So that if someone has selected USD then cell B1 is formatted 7.00USD but if the drop down is changed to GBP then it becomes 7.00GBP

If there is a way to actually use the symbols ($,£, €) then I will be even more impressed :D

 

[JugglerJAF]

Stick the following in the worksheet onject:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Range("A1")
            Case "GBP"
                Range("B1").NumberFormat = "[$£-809]#,##0.00"
            Case "USD"
                Range("B1").NumberFormat = "[$$-409]#,##0.00"
            Case "EUR"
                Range("B1").NumberFormat = "[$€-2] #,##0.00"
            Case Else
                Range("B1").NumberFormat = "#,##0.00"
        End Select
    End If
End Sub

 

[WinteE]

Add the code below to the macro object of you sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Target.NumberFormat = "#,##0.00_-[$" & Range("B1") & "]"
    End If

End Sub

Range A1:A10 is the range where numbers will be formatted when they change.
In Range B1 you enter what currency you want in the format, i.e. USD.

 

[Nyanko]

Both very interesting and genius ideas ! Thanks for thinking a way round this