gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 675
- Office Version
- 2019
- Platform
- Windows
Hello,
Using Conditional Formatting, how to highlight any matching Top 3 values in 2 different columns.
Columns L & N. Im highlighting Top 3 for both columns, but I only to highlight any matching Top 3 in the same row, if any exists.
In this example only 2 would be highlighted.
Thank you.
Using Conditional Formatting, how to highlight any matching Top 3 values in 2 different columns.
Columns L & N. Im highlighting Top 3 for both columns, but I only to highlight any matching Top 3 in the same row, if any exists.
In this example only 2 would be highlighted.
Thank you.
NBA.xlsm | |||||
---|---|---|---|---|---|
L | M | N | |||
2 | ATS | Cover2 % | |||
3 | Cover% | ATS +/- | |||
4 | 60.0% | 3.5 | 56% | ||
5 | 41.0% | -2.0 | 32% | ||
6 | 47.5% | -0.1 | 50% | ||
7 | 57.9% | 0.9 | 67% | ||
8 | 46.2% | -1.4 | 60% | ||
9 | 47.5% | -1.8 | 50% | ||
10 | 52.6% | 1.7 | 35% | ||
11 | 53.5% | 1.0 | 53% | ||
12 | 48.8% | -1.4 | 45% | ||
13 | 46.2% | -3.1 | 70% | ||
14 | 52.5% | 1.7 | 44% | ||
15 | 56.4% | 1.4 | 67% | ||
16 | 35.9% | -2.3 | 32% | ||
17 | 47.6% | -2.0 | 45% | ||
18 | |||||
19 | |||||
20 | |||||
21 | |||||
22 | |||||
23 | |||||
24 | |||||
25 | |||||
26 | |||||
27 | |||||
28 | |||||
29 | |||||
30 | |||||
31 | |||||
32 | |||||
33 | |||||
Favs |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L4:L7 | L4 | =IFERROR(VLOOKUP(A4,TeamRankings!$AL$2:$AO$33,4,0),"") |
M4:M33 | M4 | =IFERROR(VLOOKUP($A4,TeamRankings!$AL$3:$AQ$33,6,0),"") |
N4:N33 | N4 | =IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BL$65,4,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BL$32,4,0),2)),"") |
L8 | L8 | =IFERROR(VLOOKUP(A8,TeamRankings!AL2:AO33,4,0),"") |
L9 | L9 | =IFERROR(VLOOKUP(A9,TeamRankings!AL2:AO33,4,0),"") |
L10 | L10 | =IFERROR(VLOOKUP(A10,TeamRankings!AL2:AO33,4,0),"") |
L11 | L11 | =IFERROR(VLOOKUP(A11,TeamRankings!AL2:AO33,4,0),"") |
L12 | L12 | =IFERROR(VLOOKUP(A12,TeamRankings!AL2:AO33,4,0),"") |
L13 | L13 | =IFERROR(VLOOKUP(A13,TeamRankings!AL2:AO33,4,0),"") |
L14 | L14 | =IFERROR(VLOOKUP(A14,TeamRankings!AL2:AO33,4,0),"") |
L15 | L15 | =IFERROR(VLOOKUP(A15,TeamRankings!AL2:AO33,4,0),"") |
L16 | L16 | =IFERROR(VLOOKUP(A16,TeamRankings!AL2:AO33,4,0),"") |
L17 | L17 | =IFERROR(VLOOKUP(A17,TeamRankings!AL2:AO33,4,0),"") |
L18 | L18 | =IFERROR(VLOOKUP(A18,TeamRankings!AL2:AO33,4,0),"") |
L19 | L19 | =IFERROR(VLOOKUP(A19,TeamRankings!AL2:AO33,4,0),"") |
L20 | L20 | =IFERROR(VLOOKUP(A20,TeamRankings!AL2:AO33,4,0),"") |
L21 | L21 | =IFERROR(VLOOKUP(A21,TeamRankings!AL2:AO33,4,0),"") |
L22 | L22 | =IFERROR(VLOOKUP(A22,TeamRankings!AL2:AO33,4,0),"") |
L23 | L23 | =IFERROR(VLOOKUP(A23,TeamRankings!AL2:AO33,4,0),"") |
L24 | L24 | =IFERROR(VLOOKUP(A24,TeamRankings!AL2:AO33,4,0),"") |
L25 | L25 | =IFERROR(VLOOKUP(A25,TeamRankings!AL2:AO33,4,0),"") |
L26 | L26 | =IFERROR(VLOOKUP(A26,TeamRankings!AL2:AO33,4,0),"") |
L27 | L27 | =IFERROR(VLOOKUP(A27,TeamRankings!AL2:AO33,4,0),"") |
L28,L32,L30 | L28 | =IFERROR(VLOOKUP(A28,TeamRankings!AL2:AO33,4,0),"") |
L29,L33,L31 | L29 | =IFERROR(VLOOKUP($A29,TeamRankings!$AL$2:$AO$33,4,0),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
L4:L33 | Cell Value | top 3 values | text | NO |
N4:N33 | Cell Value | top 3 values | text | NO |
N6,N8,N10,N12,N14,N16,N18,N20,N22,N24,N26,N28,N30,N32 | Expression | =AND($B6<0,$N6>0.55,$O6>0,$O6>$O7,$P6>$P7,$R6>$R7,$A6=$AH6,$AI6=TRUE,$AN6<=$AN$1) | text | NO |
N6,N8,N10,N12,N14,N16,N18,N20,N22,N24,N26,N28,N30,N32 | Expression | =AND($B6<0,$N6>0.55,$J6<$J7,$O6>0,$O6>$O7,$P6>$P7,$R6>$R7,$T6>$T7,$V6>$V7,$W6>$W7,$X6>$X7,$Y6>$Y7,$Z6>$Z7,$AI6=TRUE,$AN6<$AN$1) | text | NO |
N6,N8,N10,N12,N14,N16,N18,N20,N22,N24,N26,N28,N30,N32 | Expression | =AND($B6<0,$N6>0.55,$J6<$J7,$O6>0,$O6>$O7,$P6>$P7,$R6>$R7,$T6>$T7,$X6>$X7,$Z6>$Z7,$AI6=TRUE,$AN6<$AN$1) | text | NO |
N6,N8,N10,N12,N14,N16,N18,N20,N22,N24,N26,N28,N30,N32 | Expression | =AND($B6<0,$N6>0.55,$J6<$J7,$O6>0,$O6>$O7,$P6>$P7,$R6>$R7,$X6>$X7,$A6=$AH6,$AI6=TRUE,$AN6<=$AN$1) | text | NO |
N6,N8,N10,N12,N14,N16,N18,N20,N22,N24,N26,N28,N30,N32 | Expression | =AND($B6<0,$N6>0.55,$J6<$J7,$K6>$K7,$O6>0,$O6>$O7,$P6>$P7,$R6>$R7,$A6=$AH6,$AI6=TRUE,$AN6<=$AN$1) | text | NO |
N7,N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33 | Expression | =AND($B7<0,$N7>0.55,$O7>0,$O6<$O7,$P6<$P7,$R6<$R7,$A7=$AH7,$AI7=TRUE,$AN7<=$AN$1) | text | NO |
N7,N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33 | Expression | =AND($B7<0,$N7>0.55,$J6>$J7,$O7>0,$O6<$O7,$P6<$P7,$R6<$R7,$T6<$T7,$V6<$V7,$W6<$W7,$X6<$X7,$Y6<$Y7,$Z6<$Z7,$AI7=TRUE,$AN7<$AN$1) | text | NO |
N7,N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33 | Expression | =AND($B7<0,$N7>0.55,$J6>$J7,$O7>0,$O6<$O7,$P6<$P7,$R6<$R7,$T6<$T7,$X6<$X7,$Z6<$Z7,$AI7=TRUE,$AN7<$AN$1) | text | NO |
N7,N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33 | Expression | =AND($B7<0,$N7>0.55,$J6>$J7,$O7>0,$O6<$O7,$P6<$P7,$R6<$R7,$X6<$X7,$A7=$AH7,$AI7=TRUE,$AN7<=$AN$1) | text | NO |
N7,N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33 | Expression | =AND($B7<0,$N7>0.55,$J6>$J7,$K6<$K7,$O7>0,$O6<$O7,$P6<$P7,$R6<$R7,$A7=$AH7,$AI7=TRUE,$AN7<=$AN$1) | text | NO |
J4:AF33 | Cell | contains a blank value | text | NO |
N4:P4,R4,AH4,AI4 | Expression | =AND($B4<0,$N4>0.55,$O4>0,$O4>$O5,$P4>$P5,$R4>$R5,$A4=$AH4,$AI4=TRUE,$AN4<=$AN$1) | text | NO |
J4,N4:P4,R4,T4,V4:Z4,AH4,AI4 | Expression | =AND($B4<0,$N4>0.55,$J4<$J5,$O4>0,$O4>$O5,$P4>$P5,$R4>$R5,$T4>$T5,$V4>$V5,$W4>$W5,$X4>$X5,$Y4>$Y5,$Z4>$Z5,$AI4=TRUE,$AN4<$AN$1) | text | NO |
J4,N4:P4,R4,T4,X4,Z4,AH4,AI4 | Expression | =AND($B4<0,$N4>0.55,$J4<$J5,$O4>0,$O4>$O5,$P4>$P5,$R4>$R5,$T4>$T5,$X4>$X5,$Z4>$Z5,$AI4=TRUE,$AN4<$AN$1) | text | NO |
J4,N4:P4,R4,X4,AH4,AI4 | Expression | =AND($B4<0,$N4>0.55,$J4<$J5,$O4>0,$O4>$O5,$P4>$P5,$R4>$R5,$X4>$X5,$A4=$AH4,$AI4=TRUE,$AN4<=$AN$1) | text | NO |
J4:K4,N4:P4,R4,AH4,AI4 | Expression | =AND($B4<0,$N4>0.55,$J4<$J5,$K4>$K5,$O4>0,$O4>$O5,$P4>$P5,$R4>$R5,$A4=$AH4,$AI4=TRUE,$AN4<=$AN$1) | text | NO |
J5,N5:P5,R5,T5,V5:Z5,AH5,AI5 | Expression | =AND($B5<0,$N5>0.55,$J4>$J5,$O5>0,$O4<$O5,$P4<$P5,$R4<$R5,$T4<$T5,$V4<$V5,$W4<$W5,$X4<$X5,$Y4<$Y5,$Z4<$Z5,$AI5=TRUE,$AN5<$AN$1) | text | NO |
J5,N5:P5,R5,T5,X5,Z5,AH5,AI5 | Expression | =AND($B5<0,$N5>0.55,$J4>$J5,$O5>0,$O4<$O5,$P4<$P5,$R4<$R5,$T4<$T5,$X4<$X5,$Z4<$Z5,$AI5=TRUE,$AN5<$AN$1) | text | NO |
J5,N5:P5,R5,X5,AH5,AI5 | Expression | =AND($B5<0,$N5>0.55,$J4>$J5,$O5>0,$O4<$O5,$P4<$P5,$R4<$R5,$X4<$X5,$A5=$AH5,$AI5=TRUE,$AN5<=$AN$1) | text | NO |
J5:K5,N5:P5,R5,AH5,AI5 | Expression | =AND($B5<0,$N5>0.55,$J4>$J5,$K4<$K5,$O5>0,$O4<$O5,$P4<$P5,$R4<$R5,$A5=$AH5,$AI5=TRUE,$AN5<=$AN$1) | text | NO |
N5:P5,R5,AH5,AI5 | Expression | =AND($B5<0,$N5>0.55,$O5>0,$O4<$O5,$P4<$P5,$R4<$R5,$A5=$AH5,$AI5=TRUE,$AN5<=$AN$1) | text | NO |
L2,L3:O3 | Expression | =$AY$3="020" | text | NO |
J4:AB33 | Expression | =AND(ISODD(ROW(J4)),J4=J3) | text | NO |
J4:AB33 | Expression | =AND(ISEVEN(ROW(J4)),J4=J5) | text | NO |
K4:P33,R4:R33,T4:AB33 | Expression | =K4=MAX(OFFSET(K$4,FLOOR(ROW(K4)-ROW(K$4),2),0,2,1)) | text | NO |
M4:M33 | Cell Value | <=0 | text | NO |
M4:M33 | Cell Value | >0 | text | NO |