Input CURRENCY SYMBOL to format all #'s the same currency

CWISE

New Member
Joined
Aug 29, 2005
Messages
5
Hi,

I have a spreadsheet where the user needs to input the currency symbol (i.e. USD, EUR, MXN, etc). Based upon what currency they put in the input into the cell, I am needing to format all numbers within certain cells to be the same currency. The currency will change multiple times so the currency symbol and number have to be dynamic.

Any ideas? And how I would even begin to look this excel function up in "help". I went to datapigtechnologies.com and although it skirted around formating functions- I never seem to what I needed in this instance.

Appreciate the help.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
When you say you want the numbers to change are you are saying you would want it to update to whatever the current exchange rte is? (Ex $1.00 = £0.57 = ¥118.49)
 

CWISE

New Member
Joined
Aug 29, 2005
Messages
5
No - it is more simple than that.

Basically in cell D10 the user is to input MXN or USD or EUR whichever currency applies.

For some 10 other cells that are numbers, I am needing to format the numbers to be in the same currency.

Right now the user has to input the answer in D10 and manually format each cell (format cells/numbers/currency/MXN, etc) to have the currency symbol display with the number.

I am wanting the spreadsheet to automatically format the other cells based upon what is input into D10.
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
OK you could do that by macro or by formula the pro to macro is that it would leave the numbers in number format the con is of course you have to mess with code. You can do it with az formula too but it would be a little cumbersome. I would need to know how your sheet was laid out and what it was supposed to be showing. Any prefrence here?
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Re: Input CURRENCY SYMBOL to format all #'s the same currenc

CWISE said:
Hi,

I have a spreadsheet where the user needs to input the currency symbol (i.e. USD, EUR, MXN, etc). Based upon what currency they put in the input into the cell, I am needing to format all numbers within certain cells to be the same currency. The currency will change multiple times so the currency symbol and number have to be dynamic.

Any ideas? And how I would even begin to look this excel function up in "help". I went to datapigtechnologies.com and although it skirted around formating functions- I never seem to what I needed in this instance.

Appreciate the help.
Hi CWISE:

To automate it try the following Worksheet_Change event code ...
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'let us say the range of cells to be formated is A8:b12
    If Target.Cells.Address = "$D$10" Then
        Range("a8:b12").NumberFormat = """" & [d10] & """ 0.00"""""
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,731
Messages
5,573,880
Members
412,555
Latest member
mark84
Top