| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tough conditional formatting question
Hi all,
I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
Unless I'm missing something, wouldn't this work?:
Select A2 <Format<Conditional Formatting<Condition 1 Cell Formula is: =COUNTIF(B2:D2,0)0 Click the [Format...] button and set the format you want Does that help? *********** Regards, Ron XL2002, WinXP-Pro "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition). You could insert a column inbetween columns A and B, and insert this formula =IF(C1<0,"X","")&IF(D1<0,"Y","")&IF(E1<0,"Z","" ) It would look like this Criteria XYZ X Y Z SUM a YZ 0 1 3 4 b XZ 1 0 5 6 c XY 7 9 0 16 d Z 0 0 2 2 e Y 0 7 0 7 f X 4 0 0 4 "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen something like that 0 after the parentheses before, what does that do? Thanks! "Ron Coderre" wrote: Unless I'm missing something, wouldn't this work?: Select A2 <Format<Conditional Formatting<Condition 1 Cell Formula is: =COUNTIF(B2:D2,0)0 Click the [Format...] button and set the format you want Does that help? *********** Regards, Ron XL2002, WinXP-Pro "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
No offence Ron, but this won't work. the formula counts the number of times
0 appears in the three columns, and compares that count to 0. If the count is greater than zero, then the formula results in TRUE and triggers the conditional format. So basically you will get the same format for all cases except when all three columns are non-zero. "jezzica85" wrote: I'd like to have a different format for each of those conditions I put down, not the same one for each. Would this formula do that? I've never seen something like that 0 after the parentheses before, what does that do? Thanks! "Ron Coderre" wrote: Unless I'm missing something, wouldn't this work?: Select A2 <Format<Conditional Formatting<Condition 1 Cell Formula is: =COUNTIF(B2:D2,0)0 Click the [Format...] button and set the format you want Does that help? *********** Regards, Ron XL2002, WinXP-Pro "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
Thanks a lot, Sloth, that worked great!
"Sloth" wrote: you can't do this with conditional formatting, because you can only have up to three conditions (four if you include the default condition). You could insert a column inbetween columns A and B, and insert this formula =IF(C1<0,"X","")&IF(D1<0,"Y","")&IF(E1<0,"Z","" ) It would look like this Criteria XYZ X Y Z SUM a YZ 0 1 3 4 b XZ 1 0 5 6 c XY 7 9 0 16 d Z 0 0 2 2 e Y 0 7 0 7 f X 4 0 0 4 "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
Understood, Sloth... No offense taken. I just wanted to make sure the OP
didn't want the same CF for each case (which effectively summarized to a zero in any of the columns) since no list of specific formats was mentioned. *********** Regards, Ron XL2002, WinXP-Pro "Sloth" wrote: No offence Ron, but this won't work. the formula counts the number of times 0 appears in the three columns, and compares that count to 0. If the count is greater than zero, then the formula results in TRUE and triggers the conditional format. So basically you will get the same format for all cases except when all three columns are non-zero. "jezzica85" wrote: I'd like to have a different format for each of those conditions I put down, not the same one for each. Would this formula do that? I've never seen something like that 0 after the parentheses before, what does that do? Thanks! "Ron Coderre" wrote: Unless I'm missing something, wouldn't this work?: Select A2 <Format<Conditional Formatting<Condition 1 Cell Formula is: =COUNTIF(B2:D2,0)0 Click the [Format...] button and set the format you want Does that help? *********** Regards, Ron XL2002, WinXP-Pro "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
Actually, there's one more question I just thought of--is there an easy way
to list the individual criteria in alphabetical order that have, say, X in the second column? Thanks again! "jezzica85" wrote: Thanks a lot, Sloth, that worked great! "Sloth" wrote: you can't do this with conditional formatting, because you can only have up to three conditions (four if you include the default condition). You could insert a column inbetween columns A and B, and insert this formula =IF(C1<0,"X","")&IF(D1<0,"Y","")&IF(E1<0,"Z","" ) It would look like this Criteria XYZ X Y Z SUM a YZ 0 1 3 4 b XZ 1 0 5 6 c XY 7 9 0 16 d Z 0 0 2 2 e Y 0 7 0 7 f X 4 0 0 4 "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Tough conditional formatting question
You can use a standard Data-Sort to alphabetize the Criteria column, and
then use Data-Filter-Autofilter to filter out everything but X's. "jezzica85" wrote: Actually, there's one more question I just thought of--is there an easy way to list the individual criteria in alphabetical order that have, say, X in the second column? Thanks again! "jezzica85" wrote: Thanks a lot, Sloth, that worked great! "Sloth" wrote: you can't do this with conditional formatting, because you can only have up to three conditions (four if you include the default condition). You could insert a column inbetween columns A and B, and insert this formula =IF(C1<0,"X","")&IF(D1<0,"Y","")&IF(E1<0,"Z","" ) It would look like this Criteria XYZ X Y Z SUM a YZ 0 1 3 4 b XZ 1 0 5 6 c XY 7 9 0 16 d Z 0 0 2 2 e Y 0 7 0 7 f X 4 0 0 4 "jezzica85" wrote: Hi all, I have a sequence of five columns, they look something like this: Criteria X Y Z SUM a 0 1 3 4 b 1 0 5 6 c 7 9 0 16 d 0 0 2 2 e 0 7 0 7 f 4 0 0 4 Is there a way to use conditional formatting to mark the entry in the criteria column for these conditions: Nonzero number only in first column Nonzero number only in second column Nonzero number only in third column Nonzero number only in first and second column Nonzero number only in first and third column Nonzero number only in second and third column I know this is kind of a complex question, thanks in advance! PS, thanks so much to the person who helped me with my last question if he reads this, I was sick for a day and I couldn't get back here, then I couldn't find my old post to thank you! jezzica85 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting - Formulas | Excel Discussion (Misc queries) | |||
| Conditional Formatting | Excel Discussion (Misc queries) | |||
| Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
| Conditional Formatting Cell < Today() | Excel Worksheet Functions | |||
| Conditional Formatting | Excel Discussion (Misc queries) | |||
Linear Mode
