Conditional Formatting

[Chris 21222222]

Is there a way to have a list validation that allows the box to change
currencies. For example, if i have a validation stating USD, CAD and I click
USD. And type in an amount it will come up as $1,222 or C$1000 depending on
what i choose? Any information would be greatly appreciated.

 

[Gord Dibben]

Not through Conditional Fomatting and not in one cell.

Would you need to use the numbers later for calculation?

If not, you could use a formula in a helper cell to multiply by the exchange
rate and insert the TEXT function to format the cells as $ or C$ depending
upon which was selected from the DV dropdown.

Place formula in C1

=IF(A1="USD",TEXT(B1*$E$1,"$#,##0.00"),TEXT(B1,"C$#,##0.00"))

A1 has the dropdown with USD or CAD as choices.

B1 is where you enter the amount of 1000. If CAD is selected in A1, the
1000 will remain and become formatted as a text value of C$1000.00

E1 is the cell with the exchange rate.

If USD is selected, the 1000 is multiplied by the exchange rate and
displayed as $1222.00

Otherwise I would suggest using event code which could do the above in the
cell in which you enter the amount............no need for a helper cell and
the values would be actual numbers......not text.


Gord Dibben MS Excel MVP