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

[CWISE]

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.

 

[Oorang]

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]

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]

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]

Re: Input CURRENCY SYMBOL to format all #'s the same currenc

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 ...

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

 

[Oorang]

i think that would be optimum