Conditional formatting for changing data
 

I hope this makes sense. I just got Excel 2007. I have conditional
formatting that will change the format of a column with a currency value
based on another column's text saying what the currency is. (IE, column A is
currency type, column B is value. Formatting rule for B1 says =A1="dollar"
format should be $1.00, =A1="euro" format should be in euros, etc.)

It works fine, once, when I enter the currency type. But if I try to change
it, it doesn't work anymore. Unless I open the rules again and click ok;
then it works again, and will once more if I change the currency. But it
won't do it more than once. I can't figure out why or what I'm doing wrong.
All my other conditional formats work, no matter how much I change the data.
But this one just won't....

Conditional formatting for changing data
 

i tried what you said and it works fine for me.

Here is what I did
Entered Dollar in A1, Euro in A2
Entered some numbers in B1, B2
Selected Col B
Chose Conditional Formatting
For Condition 1 entered
=A1="Dollar" and chose formatting as Custom|"Dollar" 0.00
For Condition 2 entered
=A1="Euro" and chose formatting as Custom|"Euro" 0.00

Got the appropriate formatting in B1 and B2
Now if I change A1 (or A2) to Dollar or Euro B1 (or B2) changes accordingly.

What are you doing differently?




"Mariliz" wrote:

I hope this makes sense. I just got Excel 2007. I have conditional
formatting that will change the format of a column with a currency value
based on another column's text saying what the currency is. (IE, column A is
currency type, column B is value. Formatting rule for B1 says =A1="dollar"
format should be $1.00, =A1="euro" format should be in euros, etc.)

It works fine, once, when I enter the currency type. But if I try to change
it, it doesn't work anymore. Unless I open the rules again and click ok;
then it works again, and will once more if I change the currency. But it
won't do it more than once. I can't figure out why or what I'm doing wrong.
All my other conditional formats work, no matter how much I change the data.
But this one just won't....

Conditional formatting for changing data
 

I'm not sure--I have the currency choices in a drop down (so that you can
only enter a valid currency type.) And like I said, it works the first time
or two, but it stops working after that. I have other cells that convert the
total from the original currency to dollars, based on the drop down and those
work fine no matter how often I change the drop down. But if, for example, I
have dollar it'll format in dollars. I change to euro, sometimes it changes
the format to euro. I change back to dollar and it doesn't do anything to
the format. I'm totally stumped--all the other conditional formatting I have
in the workbook works properly.

"Sheeloo" wrote:

i tried what you said and it works fine for me.

Here is what I did
Entered Dollar in A1, Euro in A2
Entered some numbers in B1, B2
Selected Col B
Chose Conditional Formatting
For Condition 1 entered
=A1="Dollar" and chose formatting as Custom|"Dollar" 0.00
For Condition 2 entered
=A1="Euro" and chose formatting as Custom|"Euro" 0.00

Got the appropriate formatting in B1 and B2
Now if I change A1 (or A2) to Dollar or Euro B1 (or B2) changes accordingly.

What are you doing differently?




"Mariliz" wrote:

I hope this makes sense. I just got Excel 2007. I have conditional
formatting that will change the format of a column with a currency value
based on another column's text saying what the currency is. (IE, column A is
currency type, column B is value. Formatting rule for B1 says =A1="dollar"
format should be $1.00, =A1="euro" format should be in euros, etc.)

It works fine, once, when I enter the currency type. But if I try to change
it, it doesn't work anymore. Unless I open the rules again and click ok;
then it works again, and will once more if I change the currency. But it
won't do it more than once. I can't figure out why or what I'm doing wrong.
All my other conditional formats work, no matter how much I change the data.
But this one just won't....

Conditional formatting for changing data
 

If drop down is in cell A1 and you are referencing it to format B1 then B1
should change every time you pick a value in A1.
Try
=TRIM(A1)="dollar" to make sure that picklist does not have extra
characters.. but then it should not work even once.

Can you send the file to me?
add @hotmail.com to to_sheeloo to get my id
(yes to_ is part of the id)

Do indicate the Excel version...

"Mariliz" wrote:

I'm not sure--I have the currency choices in a drop down (so that you can
only enter a valid currency type.) And like I said, it works the first time
or two, but it stops working after that. I have other cells that convert the
total from the original currency to dollars, based on the drop down and those
work fine no matter how often I change the drop down. But if, for example, I
have dollar it'll format in dollars. I change to euro, sometimes it changes
the format to euro. I change back to dollar and it doesn't do anything to
the format. I'm totally stumped--all the other conditional formatting I have
in the workbook works properly.

"Sheeloo" wrote:

i tried what you said and it works fine for me.

Here is what I did
Entered Dollar in A1, Euro in A2
Entered some numbers in B1, B2
Selected Col B
Chose Conditional Formatting
For Condition 1 entered
=A1="Dollar" and chose formatting as Custom|"Dollar" 0.00
For Condition 2 entered
=A1="Euro" and chose formatting as Custom|"Euro" 0.00

Got the appropriate formatting in B1 and B2
Now if I change A1 (or A2) to Dollar or Euro B1 (or B2) changes accordingly.

What are you doing differently?




"Mariliz" wrote:

I hope this makes sense. I just got Excel 2007. I have conditional
formatting that will change the format of a column with a currency value
based on another column's text saying what the currency is. (IE, column A is
currency type, column B is value. Formatting rule for B1 says =A1="dollar"
format should be $1.00, =A1="euro" format should be in euros, etc.)

It works fine, once, when I enter the currency type. But if I try to change
it, it doesn't work anymore. Unless I open the rules again and click ok;
then it works again, and will once more if I change the currency. But it
won't do it more than once. I can't figure out why or what I'm doing wrong.
All my other conditional formats work, no matter how much I change the data.
But this one just won't....

Conditional formatting for changing data
 

Unfortunately, I can't send the file. I was playing around a bit more and
have discovered that it's only the conditional formatting when the format is
a currency that isn't working. If i have the format change the color of the
cell, for example, it works no matter how much I change it back and forth.
But the currency format won't change with it.

"Sheeloo" wrote:

If drop down is in cell A1 and you are referencing it to format B1 then B1
should change every time you pick a value in A1.
Try
=TRIM(A1)="dollar" to make sure that picklist does not have extra
characters.. but then it should not work even once.

Can you send the file to me?
add @hotmail.com to to_sheeloo to get my id
(yes to_ is part of the id)

Do indicate the Excel version...

"Mariliz" wrote:

I'm not sure--I have the currency choices in a drop down (so that you can
only enter a valid currency type.) And like I said, it works the first time
or two, but it stops working after that. I have other cells that convert the
total from the original currency to dollars, based on the drop down and those
work fine no matter how often I change the drop down. But if, for example, I
have dollar it'll format in dollars. I change to euro, sometimes it changes
the format to euro. I change back to dollar and it doesn't do anything to
the format. I'm totally stumped--all the other conditional formatting I have
in the workbook works properly.

"Sheeloo" wrote:

i tried what you said and it works fine for me.

Here is what I did
Entered Dollar in A1, Euro in A2
Entered some numbers in B1, B2
Selected Col B
Chose Conditional Formatting
For Condition 1 entered
=A1="Dollar" and chose formatting as Custom|"Dollar" 0.00
For Condition 2 entered
=A1="Euro" and chose formatting as Custom|"Euro" 0.00

Got the appropriate formatting in B1 and B2
Now if I change A1 (or A2) to Dollar or Euro B1 (or B2) changes accordingly.

What are you doing differently?




"Mariliz" wrote:

I hope this makes sense. I just got Excel 2007. I have conditional
formatting that will change the format of a column with a currency value
based on another column's text saying what the currency is. (IE, column A is
currency type, column B is value. Formatting rule for B1 says =A1="dollar"
format should be $1.00, =A1="euro" format should be in euros, etc.)

It works fine, once, when I enter the currency type. But if I try to change
it, it doesn't work anymore. Unless I open the rules again and click ok;
then it works again, and will once more if I change the currency. But it
won't do it more than once. I can't figure out why or what I'm doing wrong.
All my other conditional formats work, no matter how much I change the data.
But this one just won't....