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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Harold Good" wrote in message
...
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

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


"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Harold Good" wrote in message
...
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