automatically change currency format based on input

DavidPonnet

New Member
Joined
Mar 16, 2017
Messages
23
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)

Rich (BB 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

Rich (BB code):
 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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top