Conditional currency formating

[Snessa]

I'm developing a configuration tool for international use. I want the user to select the curreny in one central place, whereafter all fields will be formatted using the proper currency indicator ($ or USD, £ or GBP, DKK or kr. as examples).
I would prefer using Excel standard currency formater, but I can't figure out how to select that depending on once selected currency.

I have the tried using TEXT(ROUND(number<REF value to>;num_digits);CurrencyFormat<REF to formar currency>)
where CurrencyFormat could be: #.##0 € as an example.
Doing so, the result is a text string, which I can do calculation on.

I much more would refer to the cell as a number and apply standard Format Cells -> Currency and Symbol. Doing this, the cell still is a number, on which I can do calculation. But I can't figure out how to select the formating currency sysmbol based on a cell value, having a value from a predefined table of currencies (DKK, EUR, GBP, NOK, SEK, USD)

Any clever suggestion to solve my problem would be appreciated.

Best regards

 

[Richard Schollar]

Hi

the easiest way to do this would be to apply a style to all the cells you want to format (it's in Format>Styles in xl2003 and below). You can use a worksheet_change event to change the curreny character in the particular style based on the user changing a single cell - this will cascade to all cellin the sheet that use this style. This way you can maintain the cells as numeric values.

Sound like something you can work with?

 

[RoryA]

If you are using 2007 or later you can apply number formats via conditional formatting.

 

[memorris]

I am trying to make this work for a purchase order form with no success. I have tried concatenate and although this puts the currency symbol onto each line cost it messes up the total SUM result by giving a VALUE# error. I am using Excel 2003.
Richard, I have looked styles but cannot see how to use them. I am given 6 options (Normal, percent, 2 x comma and 2 x currency). I can modify, delete or merge but not add a style. And how is the worksheet_change event used or is this a VBA procedure?