"IF" statement to format currency in Excel

[RachieD]

Hi

Column E in my spreadsheet allows the user to select the currencies "GBP" or "Euro" via a data validation list. The monetary values are entered into columns F - L. As Conditional Formating will not do it, I would like to write an "IF" Statement so that if they select Euro, the format in the other columns is €x,xxx and if they select GBP the format is £x,xxx - is this possible...?

Thanks!
RachieD

 

[fredericofonseca]

use the macro recorder to do that change for your self, and look at the code created and then adapt to your preference.

As for detecting the change of currency, either one of the change events or a formula based on that particular cell will allow you to execute the code above.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[xlbo]

Not without code AFAIK

You can't use formulae to affect other cells

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[shetlandbob]

I would recommend using a macro as these guys have said, but there is a work around using if statements. Its messy and I wouldn't necessarily advocate using it but here goes:

You need to add in extra cols for the number value to be enterered in as a number i.e.

Col E   Col F (user enter numbers in this col)  Col G
GBP     1000                                    £1000
EURO    5000                                    $1000

[green]
in Col G put the following:
=IF(E11="GBP",CONCATENATE("£",F11),IF(E11="EURO",CONCATENATE("$",F11),))
[/green]

Its messy and a macro would be much better, but it will work.



Robert Cumming

http://www.consultantengineer.co.uk

 

[xlbo]

Think the brief was so that the OP could ENTER data into these other cells so having a formula in them wouldn't be viable...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[shetlandbob]

I believe so, but as I said its messy and I wouldn't advocate it, but it does "sort of" work....

Best option is to use a macro for the on_change event in the worksheet/book

Robert Cumming

http://www.consultantengineer.co.uk