Include contents of cell in custom number format

[urungus]

Is it possible to include the contents of cell A1 in a custom number format? Suppose cell A1 contains string "USD" representing currency. Cell B1 is to have custom number format, if user types 100 in cell B1, it should be displayed as "100 USD". Then if cell A1 is changed to "GBP", cell B1 should update to show "100 GBP". Is this possible?

 

[MarcelBeug]

It might be possible with conditional formatting, but then you need to define a rule and a format for each currency.

So my suggestion would be to use VBA, like

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim i As Long
    
    Set rng = Intersect(Target, Columns(2))
    If rng Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    
    For i = 1 To rng.Count
        rng(i).NumberFormat = "#,##0.00 """ & rng(i).Offset(0, -1) & """"
    Next i
    
    On Error GoTo 0
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub