Select currency from drop down, all cells in sheet change to that currency symbol

[davejago]

I have a drop down menu in A1 e.g. Euro, $, GBP etc. I'd like for values in B1:B100 change to the appropriate currency symbol based on what the user chooses from this dropdown. How is this possible? I am not good with macros

(I have the actual exchange rate conversion figured out already).

Thanks for any & all help!

 

[GlennUK]

I do not recommend changing source values. I'd say that for safety that you have the source values stored in one area, and have a display area for showing the source in different currencies, and use a conversion formula to calculate the values, dependent on the drop-down choice. You'd need a macro if you want the number formats to use the actual currency symbol, and be real numbers ... on the other hand you could make the results be text versions of the converted values and embed the relevant currency symbol into the text by formulas without any macros needed at all. An example:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Currency Drop-down	Drop-down choices	Symbol	Exchange rate		Source data ( dollars )		Display area
2	GBP	Euro		0.690059		$41.00		25.15
3		Dollar	$	1		$208.00		127.57
4		Yen		80.6071		$25.00		15.33
5		GBP		0.613322		$208.00		127.57
6						$64.00		39.25
7						$39.00		23.92
8						$111.00		68.08
Sheet23

 

[JamieAwdry]

Hi Davejago

I use the same thing in one of my spreadsheets. In the worksheet code I have this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Currency_Calc
End Sub

Then in a module
Sub Currency_Calc()
With ActiveWorkbook.Styles("Currency")
.IncludeNumber = True
.IncludeFont = False
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
End With
If UCase([a1]) = "€" Then
ActiveWorkbook.Styles("Currency").NumberFormat = _
"[$€-2]#,##0_ ;[Red]-[$€-2]#,##0"

Else
ActiveWorkbook.Styles("Currency").NumberFormat = _
"[$£-2]#,##0_ ;[Red]-[$£-2]#,##0"
End If
End Sub

So when I change the value of cell A1 to a € it changes all currencys to that symbol with the correct formatting.
You would need to amend the macro to add in Dollar etc etc..

Let me know if you need more help. Jamie

 

[MonkeyFace28]

Jamie, I have the same issue as OP... is the only way to solve this by using a macro?