Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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....
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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....

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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....

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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....

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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....

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically Copying Changing Conditional Formatting sdm New Users to Excel 0 September 5th 08 05:42 PM
Conditional formatting: changing number format Almir Vargas[_2_] Excel Worksheet Functions 3 March 22nd 08 06:33 PM
Conditional formatting changing relative when copied to other cell robbin Excel Worksheet Functions 1 September 5th 07 11:36 PM
Changing text color usinf a formula (NOT Conditional Formatting) John Elliott Excel Discussion (Misc queries) 7 June 11th 06 07:30 AM
Changing a Formula in Conditional Formatting Dee Excel Worksheet Functions 1 November 2nd 05 05:30 PM


All times are GMT +1. The time now is 05:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"