conditional number format dependent on other cell

[Harold Good]

Hi,
From all my reading, I've determined what I want to do is not possible with
Excel 2003, at least without using VBA.

I have a spreadsheet that uses 2 currencies. One is always US $, the second
can vary, depending on what country the finance report comes to me from.

For this second currency, is there any way I can have certain cells take on,
e.g the '£' currency format when a value or format is entered in another
single cell?

For example, I'd like numbers in column B to display the '£' currency
format when a value or format is entered in A1. Or if the report comes from
Kenya, to have "KSH" preceed any numbers in column B, based on them entering
KSH in A1.

It seems a shame that Excel doesn't allow Numbers under their conditional
formating where I can enter a formula. Why not?

I've tinkered with Custom Number format, but can't make anything work.

Unless someone has a great solution, I shall have to settle for having no
currency format at all in Col B. Since these cells are sprinkled thru out
the spreadsheet, it'd be too much off a pain to have them select them and
change the format to their country each quarter.

Thanks for any thoughts you may have,
Harold

 

[Jon Peltier]

You could use a lookup to provide a number format, and use TEXT to apply it
to a value. Say the cell which you use to look up a format is A1, the lookup
table is F1:G10, and the value to display is in D1. Use a formula like this:

=TEXT(D1,VLOOKUP(A1,F1:G10,2))

You can't use this cell for calculations, but it will display nicely. Use
the cell with the actual value (D1) for any calculations.

- Jon

 

[Harold Good]

Thanks very much, I had no idea the Text function would do this! I will try
to incorporate this into my needs.
Harold