automatically change currency format based on input

[DavidPonnet]

Hello,

I am an estimator in a company that does installation worldwide. We are quoting in many different currencies, and now have to manually make sure I change the currency in my pricing column every time.

To make this easier, I am trying to find a solution:

put € or $ or £ or ..., in one cell, and have it automatically change the currency format in selected cells and columns.

Or, format the currency in 1 cell, and have it automatically change the currency format in selected cells and columns.

Not sure if that is even possible, but it would really come in handy

thx in advance

 

[jmacleary]

Hi David

I would be tempted to create a new column and hold the currency in there - I suggest using the 3-digit international code, e.g. GBP for Sterling, USD for dollars, etc. You could make this column a drop-down list of all the currencies you use. This would give you several advantages, as you could filter on this to see selected currency quotes, create sumifs to add up all the different ones, etc. The currency cells would just be formatted as numbers with 2 decimal places.

Hope this idea helps.
John

 

[DavidPonnet]

Thank you jmacleary,

I did already came up with that idea, and I changed it to that idea, but I dont really like the look and feel of it

Was hoping to find another solution, but might be to far fetched

 

[jmacleary]

Well a complicated option would be to have vba code to detect an entry being typed in the currency field and applying the relevant format to that cell. There would need to be some detection of the currency symbol.

 

[dmt32]

Hi,
If you are happy to use VBA in your workbook then give following a try & see if any help
As already suggested, use the 3-digit international codes (USD, EUR, GBP) in a data validation list.

Place the following code in your worksheets code page (right click tab > View Code)

 Private Sub Worksheet_Change(ByVal Target As Range)    
   If Target.Address = "$B$2" Then FormatAsCurrency Me.Range("D3:D20"), Target.Text
End Sub

You will need to change Target Address of Data Validation Cell shown in RED
Also, specify the Range Addresses of cells that format applies to shown in BLUE

From VBA Editor Menu select Insert > Module

Place following code in the module

 Sub FormatAsCurrency(ByVal Target As Range, ByVal CurrencyCode As String)    
    Target.NumberFormat = Choose(Application.Match(CurrencyCode, Array("USD", "EUR", "GBP"), 0), _
                            "[$$-409]#,##0.00", "[$€-2] #,##0.00", "[$£-809]#,##0.00")
    
End Sub

Return to Excel (Alt+Q)

Hope Helpful

Dave