Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically Copying Changing Conditional Formatting | New Users to Excel | |||
Conditional formatting: changing number format | Excel Worksheet Functions | |||
Conditional formatting changing relative when copied to other cell | Excel Worksheet Functions | |||
Changing text color usinf a formula (NOT Conditional Formatting) | Excel Discussion (Misc queries) | |||
Changing a Formula in Conditional Formatting | Excel Worksheet Functions |