| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting referencing 3 cells by row in a range
Hi,
I need to set up conditional formatting with 3 conditions and each condition is to look at 3 different cells in a row and determine if they meet the condition. Currently what I have setup does not limit the conditions to have all 3 in the same row...it will indicate that I have met the condition when in fact one of the values is only true if you look in different rows (see example). Here is what I have setup so far: condition 1 - AND((COUNTIF($E$19:$E$10000,$E19)1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condition 2 - AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condtion 3 - =AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($A$ 19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29) =1)) and example of the data is as follows: column a column b column c column d column e column f row 18 "Unit " "Desc" "Type" "Status" "WO" "WO Desc" row 19 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 20 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 21 1005M3 SL1 ROLL P 40 607696 Drive Service - Shutdown row 22 1006 HEAD 1 S 15 582290 replace skirting row 23 1006 HEAD 1 P 40 600453 FRMING HEAD INSPCTION row 24 1006 HEAD 1 P 40 608227 FRMING HEAD INSPCTION row 25 1006M12 ROLL 2 S 10 607840 Change rosta. row 26 1007 CONV S 40 398889 INSTALL GREASE LINES ON SCREWS row 27 1007 CONV P 40 605779 Swivel Trough Mechanical Insp row 28 1008 BIN 4 S 40 441259 CHANGE DECK row 29 1008M3 PICKER P 40 594158 Drive Service - Shutdown row 30 1008M3 PICKER P 40 603352 Pick Rolls Insp - Operating row 31 1008M3 PICKER P 40 605910 PICK ROLL DRIVE OPER-INSP I set up the conditional formatting on column E. Cell E29 is meeting condition 2 when it shouldn't because the 3 criteria cannot all be found in Row 29... Please help :) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting referencing 3 cells by row in a range
The formula for condition 3:
=AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($A $19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29 )=1)) The COUNTIF criteria refers to E29, A29 and F29. The formulas for conditions 1 and 2 refer to E19, A19 and F19. Is the formula for condition 3 correct? Condition 2 for row 29 does meet the criteria. AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) Based on the sample data: COUNTIF($E$19:$E$10000,$E19)=1 = TRUE (1 instance of 594158) COUNTIF($A$19:$A$10000,$A19)1 = TRUE (3 instances of 1008M3) COUNTIF($F$19:$F$10000,$F29)1 = TRUE (2 instances of Drive Service - Shutdown) -- Biff Microsoft Excel MVP "TheMTCGal" wrote in message ... Hi, I need to set up conditional formatting with 3 conditions and each condition is to look at 3 different cells in a row and determine if they meet the condition. Currently what I have setup does not limit the conditions to have all 3 in the same row...it will indicate that I have met the condition when in fact one of the values is only true if you look in different rows (see example). Here is what I have setup so far: condition 1 - AND((COUNTIF($E$19:$E$10000,$E19)1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condition 2 - AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condtion 3 - =AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($A$ 19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29) =1)) and example of the data is as follows: column a column b column c column d column e column f row 18 "Unit " "Desc" "Type" "Status" "WO" "WO Desc" row 19 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 20 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 21 1005M3 SL1 ROLL P 40 607696 Drive Service - Shutdown row 22 1006 HEAD 1 S 15 582290 replace skirting row 23 1006 HEAD 1 P 40 600453 FRMING HEAD INSPCTION row 24 1006 HEAD 1 P 40 608227 FRMING HEAD INSPCTION row 25 1006M12 ROLL 2 S 10 607840 Change rosta. row 26 1007 CONV S 40 398889 INSTALL GREASE LINES ON SCREWS row 27 1007 CONV P 40 605779 Swivel Trough Mechanical Insp row 28 1008 BIN 4 S 40 441259 CHANGE DECK row 29 1008M3 PICKER P 40 594158 Drive Service - Shutdown row 30 1008M3 PICKER P 40 603352 Pick Rolls Insp - Operating row 31 1008M3 PICKER P 40 605910 PICK ROLL DRIVE OPER-INSP I set up the conditional formatting on column E. Cell E29 is meeting condition 2 when it shouldn't because the 3 criteria cannot all be found in Row 29... Please help :) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting referencing 3 cells by row in a range
Argh! Copy/paste typos...
Based on the sample data: COUNTIF($E$19:$E$10000,$E29)=1 = TRUE (1 instance of 594158) COUNTIF($A$19:$A$10000,$A29)1 = TRUE (3 instances of 1008M3) COUNTIF($F$19:$F$10000,$F29)1 = TRUE (2 instances of Drive Service - Shutdown) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The formula for condition 3: =AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($ A$19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F2 9)=1)) The COUNTIF criteria refers to E29, A29 and F29. The formulas for conditions 1 and 2 refer to E19, A19 and F19. Is the formula for condition 3 correct? Condition 2 for row 29 does meet the criteria. AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) Based on the sample data: COUNTIF($E$19:$E$10000,$E19)=1 = TRUE (1 instance of 594158) COUNTIF($A$19:$A$10000,$A19)1 = TRUE (3 instances of 1008M3) COUNTIF($F$19:$F$10000,$F29)1 = TRUE (2 instances of Drive Service - Shutdown) -- Biff Microsoft Excel MVP "TheMTCGal" wrote in message ... Hi, I need to set up conditional formatting with 3 conditions and each condition is to look at 3 different cells in a row and determine if they meet the condition. Currently what I have setup does not limit the conditions to have all 3 in the same row...it will indicate that I have met the condition when in fact one of the values is only true if you look in different rows (see example). Here is what I have setup so far: condition 1 - AND((COUNTIF($E$19:$E$10000,$E19)1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condition 2 - AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condtion 3 - =AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($A$ 19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29) =1)) and example of the data is as follows: column a column b column c column d column e column f row 18 "Unit " "Desc" "Type" "Status" "WO" "WO Desc" row 19 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 20 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 21 1005M3 SL1 ROLL P 40 607696 Drive Service - Shutdown row 22 1006 HEAD 1 S 15 582290 replace skirting row 23 1006 HEAD 1 P 40 600453 FRMING HEAD INSPCTION row 24 1006 HEAD 1 P 40 608227 FRMING HEAD INSPCTION row 25 1006M12 ROLL 2 S 10 607840 Change rosta. row 26 1007 CONV S 40 398889 INSTALL GREASE LINES ON SCREWS row 27 1007 CONV P 40 605779 Swivel Trough Mechanical Insp row 28 1008 BIN 4 S 40 441259 CHANGE DECK row 29 1008M3 PICKER P 40 594158 Drive Service - Shutdown row 30 1008M3 PICKER P 40 603352 Pick Rolls Insp - Operating row 31 1008M3 PICKER P 40 605910 PICK ROLL DRIVE OPER-INSP I set up the conditional formatting on column E. Cell E29 is meeting condition 2 when it shouldn't because the 3 criteria cannot all be found in Row 29... Please help :) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting referencing 3 cells by row in a range
Your 1st and 2nd condition is perfect. But the 3rd condition is incorrect
which is described below:- condition 1 - =AND((COUNTIF($A$19:$A$10000,$A19)1),(COUNTIF($E$ 19:$E$10000,$E19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condition 2 - =AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$ 19:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) But in your 3rd condition look the countif criteria you have mentioned the Cell reference as $E29, $A29 & $F29 instead of $E19, $A19 & $F19. That is the reason you are not getting the correct conditional formatting. Your Wrong 3rd Conditional formatting Formula condtion 3 - =AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($A$ 19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29) =1)) Actual formula should be like the below for Condition 3 =AND((COUNTIF($E$19:$E$10000,$E19)1),(COUNTIF($A$ 19:$A$10000,$A19)=1),(COUNTIF($F$19:$F$10000,$F19) =1)) While applying the above three conditions Place the Cursor in A19 cell and upto F19 by holding shift and pressing the right arrow and now extend your selection upto row 10000 (i.e.) A19: F10000. Now go to FormatConditional formatting and paste the first 2 conditions and the Corrected formula in 3rd condition and apply the formatting accordingly. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "TheMTCGal" wrote: Hi, I need to set up conditional formatting with 3 conditions and each condition is to look at 3 different cells in a row and determine if they meet the condition. Currently what I have setup does not limit the conditions to have all 3 in the same row...it will indicate that I have met the condition when in fact one of the values is only true if you look in different rows (see example). Here is what I have setup so far: condition 1 - AND((COUNTIF($E$19:$E$10000,$E19)1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condition 2 - AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$1 9:$A$10000,$A19)1),(COUNTIF($F$19:$F$10000,$F19) 1)) condtion 3 - =AND((COUNTIF($E$19:$E$10000,$E29)1),(COUNTIF($A$ 19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29) =1)) and example of the data is as follows: column a column b column c column d column e column f row 18 "Unit " "Desc" "Type" "Status" "WO" "WO Desc" row 19 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 20 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING row 21 1005M3 SL1 ROLL P 40 607696 Drive Service - Shutdown row 22 1006 HEAD 1 S 15 582290 replace skirting row 23 1006 HEAD 1 P 40 600453 FRMING HEAD INSPCTION row 24 1006 HEAD 1 P 40 608227 FRMING HEAD INSPCTION row 25 1006M12 ROLL 2 S 10 607840 Change rosta. row 26 1007 CONV S 40 398889 INSTALL GREASE LINES ON SCREWS row 27 1007 CONV P 40 605779 Swivel Trough Mechanical Insp row 28 1008 BIN 4 S 40 441259 CHANGE DECK row 29 1008M3 PICKER P 40 594158 Drive Service - Shutdown row 30 1008M3 PICKER P 40 603352 Pick Rolls Insp - Operating row 31 1008M3 PICKER P 40 605910 PICK ROLL DRIVE OPER-INSP I set up the conditional formatting on column E. Cell E29 is meeting condition 2 when it shouldn't because the 3 criteria cannot all be found in Row 29... Please help :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting referencing multiple cells. | Excel Worksheet Functions | |||
| Conditional Formatting to a RANGE of cells | Excel Discussion (Misc queries) | |||
| Conditional formatting a range of cells. | Excel Discussion (Misc queries) | |||
| Conditional formatting a range of cells | Excel Worksheet Functions | |||
| Conditional formatting for range of cells? | Excel Worksheet Functions | |||
Linear Mode
