vba selecting a specific range if ISNUMBER

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
684
Office Version
  1. 2019
Platform
  1. Windows
Hello,
How can I select a range from A3:F? If C:C (IsNumber). I only want to select the given range if C:C IsNumber or IsNumeric or Not an Error (ie #N/A, #VALUE!)?
The selected range should be A3:F23.
Thank you

NBA.xlsm
ABCDEF
2As FAV As FAVRankCriteria2Criteria2 PercentageCount
3IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"Rank2, AL & AM >60%"IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"Rank2, AD & AE >60%"1Rank2, AL & AM >60%80.0%25
4,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"MOV2, AL:AM >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"MOV2, AD:AE >= 60%"2MOV2, AL:AM >= 60%64.3%28
5,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($T4),($T4>0),$W4>=$W5),"Q, R, S, T>0, W",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($T5),($T5>0),$W4<=$W5),"Q, R, S, T>0, W"3Q, R, S, T>0, W61.5%13
6,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($T4),($T4>0)),"MOV2 > ATS",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($T5),($T5>0)),"MOV2 > ATS"4MOV2 > ATS59.5%37
7,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,$S4>=$S5,ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"MOV2,MOV,AL:AM >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,$S4<=$S5,ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"MOV2,MOV,AL:AM >= 60%"5MOV2,MOV,AL:AM >= 60%58.8%34
8,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"MOV, AL:AM >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"MOV, AL:AM >= 60%"5MOV, AL:AM >= 60%58.8%34
9,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$K4<=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),ISNUMBER($T4),($T4>0)),"K,L,Q,R>0,S>0,T>0",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$K4>=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),ISNUMBER($T5),($T5>0)),"K,L,Q,R>0,S>0,T>0"7K,L,Q,R>0,S>0,T>058.3%12
10,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,$V4>=$V5,$X4>=$X5,$AA4>=$AA5),"Orange - Q,S,V,X,AA",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,$V4<=$V5,$X4<=$X5,$AA4<=$AA5),"Orange - Q,S,V,X,AA"8Orange - Q,S,V,X,AA57.1%28
11,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$AL4>=.6),"Rank2, AL >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$AL5>=.6),"Rank2, AL >= 60%"8Rank2, AL >= 60%57.1%42
12,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$M4>=$M5,ISNUMBER($N4),($N4>0),$Q4>=$Q5,ISNUMBER($R4),($R4>0)),"M,N>0, Q,R>0",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$M4<=$M5,ISNUMBER($N5),($N5>0),$Q4<=$Q5,ISNUMBER($R5),($R5>0)),"M,N>0, Q,R>0"10M,N>0, Q,R>055.6%54
13,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Y4>=$Y5),"Asst/Tover >=1.85",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Y4<=$Y5),"Asst/Tover >=1.85"11Asst/Tover >=1.8555.2%1
14,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>=0.6),ISNUMBER($S4),($S4>=6)),"Q>=60%, S>=6",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>=0.6),ISNUMBER($S5),($S5>=6)),"Q>=60%, S>=6"12Q>=60%, S>=654.1%37
15,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5),"Green - Q,S",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5),"Green - Q,S"13Green - Q,S53.7%67
16,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$L4>=$L5,$Q4>=$Q5,$S4>=$S5),"Aqua - L,Q,S",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$L4<=$L5,$Q4<=$Q5,$S4<=$S5),"Aqua - L,Q,S"13Aqua - L,Q,S53.7%67
17,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,$X4>=$X5),"Purple - Q,S,X",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,$X4<=$X5),"Purple - Q,S,X"15Purple - Q,S,X53.3%45
18,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$K4<=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),ISNUMBER($T4),($T4>0),$AC4>=$AC5),"K,L,Q,R>0,S>0,T>0,AC",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$K4>=$K5,$L4<=$L5,$Q4<=$Q5,ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),ISNUMBER($T5),($T5>0),$AC4<=$AC5),"K,L,Q,R>0,S>0,T>0,AC"16K,L,Q,R>0,S>0,T>0,AC50.0%6
19,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>=0.6)),"Cover2% > 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>=0.6)),"Cover2% > 60%"17Cover2% > 60%48.8%41
20,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$K4<=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R4),($R4>0),$X4>=$X5),"K,L,Q,R>0,X",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$K4>=$K5,$L4>=$L5,$Q4<=$Q5,ISNUMBER($R4),($R4>0),$X4<=$X5),"K,L,Q,R>0,X"18K,L,Q,R>0,X48.0%25
21,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$N4>0,$P4>0,$R4>0,$T4>0),"Navy",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$N5>0,$P5>0,$R5>0,$T5>0),"Navy"19Navy47.8%23
22,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$P4>0,$T4>0),"MOV2, MOV > ATS",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$P5>0,$T5>0),"MOV2, MOV > ATS"19MOV2, MOV > ATS47.8%23
23,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$L4>=$L$5,$X4>=$X5,$AC4>=$AC5),"K,L,Q,R>0,X,AC",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$L4<=$L$5,$X4<=$X5,$AC4<=$AC5),"K,L,Q,R>0,X,AC"21K,L,Q,R>0,X,AC39.3%28
24,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#N/AMiscFALSE1
25,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#N/AMiscFALSEHigher
26,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#VALUE!MiscFALSE1
27,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#N/AMiscFALSE1
Indicators
Cell Formulas
RangeFormula
D3:D27D3=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
E3:E27E3=IF($D3="Navy",Favs!$M$43,IF($D3="MOV2, MOV > ATS",Favs!$M$55,IF($D3="Purple - Q,S,X",Favs!$V$43,IF($D3="Green - Q,S",Favs!$P$43,IF($D3="Aqua - L,Q,S",Favs!$S$43,IF($D3="Orange - Q,S,V,X,AA",Favs!$Y$43,IF($D3="K,L,Q,R>0,S>0,T>0,AC",Favs!$AB$43,IF($D3="Rank2, AL & AM >60%",Favs!$AH$43,IF($D3="MOV, AL:AM >= 60%",Favs!$AB$67,IF($D3="MOV2, AL:AM >= 60%",Favs!$AE$67,IF($D3="MOV2,MOV,AL:AM >= 60%",Favs!$AH$67,IF($D3="K,L,Q,R>0,S>0,T>0",Favs!$P$67,IF($D3="K,L,Q,R>0,X",Favs!$M$67,IF($D3="K,L,Q,R>0,S>0,T>0,AC",Favs!$AB$43,IF($D3="K,L,Q,R>0,X,AC",Favs!$S$55,IF($D3="M,N>0, Q,R>0",Favs!$Y$79,IF($D3="Q>=60%, S>=6",Favs!$S$67,IF($D3="Q, R, S, T>0, W",Favs!$M$79,IF($D3="EPR (AH)",Favs!$M$91,IF($D3="MOV2 > ATS",Favs!$P$55,IF($D3="Adj OFF & DEF (AI, AJ)",Favs!$P$91,IF($D3="Asst/Tover >=1.85",Favs!$S$79,IF($D3="Poss/G (AD)",Favs!$S$91,IF($D3="K, R>0, T>0",Favs!$V$79,IF($D3="Tovers (AE)",Favs!$V$91,IF($D3="Steals (AF)",Favs!$Y$91,IF($D3="Rank2, AL >= 60%",Favs!$AB$55,IF($D3="Fouls (AG)",Favs!$AB$91,IF($D3="Cover2% > 60%",Favs!$AE$43,IF($D3="Rank2, AM >= 60%",Favs!$AE$55,IF($D3="Adj Off. (AI)",Favs!$AE$91,IF($D3="AC, AL & AM>= 60%",Favs!$AH$55,IF($D3="Adj Def. (AJ)",Favs!$AH$91)))))))))))))))))))))))))))))))))
F3F3=Favs!$AH$48
F4F4=Favs!$AE$72
F5F5=Favs!M84
F6F6=Favs!P60
F7F7=Favs!$AH$72
F8F8=Favs!$AB$72
F9F9=Favs!$P$72
F10F10=Favs!$Y$48
F11F11=Favs!AB60
F12F12=Favs!$Y$84
F13F13=Favs!AH64
F14F14=Favs!$S$72
F15F15=Favs!$P$48
F16F16=Favs!$S$48
F17F17=Favs!$V$48
F18F18=Favs!$AB$48
F19F19=Favs!AE48
F20F20=Favs!$M$72
F21F21=Favs!$M$48
F22F22=Favs!$M$60
F23F23=Favs!$S$60
F24:F27F24=Favs!AH76
C27,C3:C25C3=RANK(E3,Rank)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
gtd526 said:
Hello,
How can I select a range from A3:F? If C:C (IsNumber). I only want to select the given range if C:C IsNumber or IsNumeric or Not an Error (ie #N/A, #VALUE!)?
The selected range should be A3:F23.
Thank you

NBA.xlsm
ABCDEF
2As FAV As FAVRankCriteria2Criteria2 PercentageCount
3IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"Rank2, AL & AM >60%"IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"Rank2, AD & AE >60%"1Rank2, AL & AM >60%80.0%25
4,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"MOV2, AL:AM >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"MOV2, AD:AE >= 60%"2MOV2, AL:AM >= 60%64.3%28
5,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($T4),($T4>0),$W4>=$W5),"Q, R, S, T>0, W",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($T5),($T5>0),$W4<=$W5),"Q, R, S, T>0, W"3Q, R, S, T>0, W61.5%13
6,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($T4),($T4>0)),"MOV2 > ATS",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($T5),($T5>0)),"MOV2 > ATS"4MOV2 > ATS59.5%37
7,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,$S4>=$S5,ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"MOV2,MOV,AL:AM >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,$S4<=$S5,ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"MOV2,MOV,AL:AM >= 60%"5MOV2,MOV,AL:AM >= 60%58.8%34
8,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,ISNUMBER($AD4),($AD4>=.6),ISNUMBER($AE4),($AE4>=.6)),"MOV, AL:AM >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,ISNUMBER($AD5),($AD5>=.6),ISNUMBER($AE5),($AE5>=.6)),"MOV, AL:AM >= 60%"5MOV, AL:AM >= 60%58.8%34
9,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$K4<=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),ISNUMBER($T4),($T4>0)),"K,L,Q,R>0,S>0,T>0",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$K4>=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),ISNUMBER($T5),($T5>0)),"K,L,Q,R>0,S>0,T>0"7K,L,Q,R>0,S>0,T>058.3%12
10,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,$V4>=$V5,$X4>=$X5,$AA4>=$AA5),"Orange - Q,S,V,X,AA",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,$V4<=$V5,$X4<=$X5,$AA4<=$AA5),"Orange - Q,S,V,X,AA"8Orange - Q,S,V,X,AA57.1%28
11,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$AL4>=.6),"Rank2, AL >= 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$AL5>=.6),"Rank2, AL >= 60%"8Rank2, AL >= 60%57.1%42
12,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$M4>=$M5,ISNUMBER($N4),($N4>0),$Q4>=$Q5,ISNUMBER($R4),($R4>0)),"M,N>0, Q,R>0",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$M4<=$M5,ISNUMBER($N5),($N5>0),$Q4<=$Q5,ISNUMBER($R5),($R5>0)),"M,N>0, Q,R>0"10M,N>0, Q,R>055.6%54
13,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Y4>=$Y5),"Asst/Tover >=1.85",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Y4<=$Y5),"Asst/Tover >=1.85"11Asst/Tover >=1.8555.2%1
14,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>=0.6),ISNUMBER($S4),($S4>=6)),"Q>=60%, S>=6",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>=0.6),ISNUMBER($S5),($S5>=6)),"Q>=60%, S>=6"12Q>=60%, S>=654.1%37
15,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5),"Green - Q,S",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5),"Green - Q,S"13Green - Q,S53.7%67
16,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$L4>=$L5,$Q4>=$Q5,$S4>=$S5),"Aqua - L,Q,S",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$L4<=$L5,$Q4<=$Q5,$S4<=$S5),"Aqua - L,Q,S"13Aqua - L,Q,S53.7%67
17,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,$X4>=$X5),"Purple - Q,S,X",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,$X4<=$X5),"Purple - Q,S,X"15Purple - Q,S,X53.3%45
18,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$K4<=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),ISNUMBER($T4),($T4>0),$AC4>=$AC5),"K,L,Q,R>0,S>0,T>0,AC",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$K4>=$K5,$L4<=$L5,$Q4<=$Q5,ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),ISNUMBER($T5),($T5>0),$AC4<=$AC5),"K,L,Q,R>0,S>0,T>0,AC"16K,L,Q,R>0,S>0,T>0,AC50.0%6
19,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>=0.6)),"Cover2% > 60%",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>=0.6)),"Cover2% > 60%"17Cover2% > 60%48.8%41
20,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$K4<=$K5,$L4>=$L5,$Q4>=$Q5,ISNUMBER($R4),($R4>0),$X4>=$X5),"K,L,Q,R>0,X",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$K4>=$K5,$L4>=$L5,$Q4<=$Q5,ISNUMBER($R4),($R4>0),$X4<=$X5),"K,L,Q,R>0,X"18K,L,Q,R>0,X48.0%25
21,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$N4>0,$P4>0,$R4>0,$T4>0),"Navy",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$N5>0,$P5>0,$R5>0,$T5>0),"Navy"19Navy47.8%23
22,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$P4>0,$T4>0),"MOV2, MOV > ATS",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$P5>0,$T5>0),"MOV2, MOV > ATS"19MOV2, MOV > ATS47.8%23
23,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$L4>=$L$5,$X4>=$X5,$AC4>=$AC5),"K,L,Q,R>0,X,AC",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$L4<=$L$5,$X4<=$X5,$AC4<=$AC5),"K,L,Q,R>0,X,AC"21K,L,Q,R>0,X,AC39.3%28
24,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#N/AMiscFALSE1
25,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#N/AMiscFALSEHigher
26,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#VALUE!MiscFALSE1
27,IF(AND($A4=$AI4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),$S4>=$S5,$AJ4=TRUE,$AO4<=$AO$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0))),"Misc",IF(AND($A5=$AI5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),$S4<=$S5,$AJ5=TRUE,$AO5<=$AO$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0))),"Misc"#N/AMiscFALSE1
Indicators
Cell Formulas
RangeFormula
D3:D27D3=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
E3:E27E3=IF($D3="Navy",Favs!$M$43,IF($D3="MOV2, MOV > ATS",Favs!$M$55,IF($D3="Purple - Q,S,X",Favs!$V$43,IF($D3="Green - Q,S",Favs!$P$43,IF($D3="Aqua - L,Q,S",Favs!$S$43,IF($D3="Orange - Q,S,V,X,AA",Favs!$Y$43,IF($D3="K,L,Q,R>0,S>0,T>0,AC",Favs!$AB$43,IF($D3="Rank2, AL & AM >60%",Favs!$AH$43,IF($D3="MOV, AL:AM >= 60%",Favs!$AB$67,IF($D3="MOV2, AL:AM >= 60%",Favs!$AE$67,IF($D3="MOV2,MOV,AL:AM >= 60%",Favs!$AH$67,IF($D3="K,L,Q,R>0,S>0,T>0",Favs!$P$67,IF($D3="K,L,Q,R>0,X",Favs!$M$67,IF($D3="K,L,Q,R>0,S>0,T>0,AC",Favs!$AB$43,IF($D3="K,L,Q,R>0,X,AC",Favs!$S$55,IF($D3="M,N>0, Q,R>0",Favs!$Y$79,IF($D3="Q>=60%, S>=6",Favs!$S$67,IF($D3="Q, R, S, T>0, W",Favs!$M$79,IF($D3="EPR (AH)",Favs!$M$91,IF($D3="MOV2 > ATS",Favs!$P$55,IF($D3="Adj OFF & DEF (AI, AJ)",Favs!$P$91,IF($D3="Asst/Tover >=1.85",Favs!$S$79,IF($D3="Poss/G (AD)",Favs!$S$91,IF($D3="K, R>0, T>0",Favs!$V$79,IF($D3="Tovers (AE)",Favs!$V$91,IF($D3="Steals (AF)",Favs!$Y$91,IF($D3="Rank2, AL >= 60%",Favs!$AB$55,IF($D3="Fouls (AG)",Favs!$AB$91,IF($D3="Cover2% > 60%",Favs!$AE$43,IF($D3="Rank2, AM >= 60%",Favs!$AE$55,IF($D3="Adj Off. (AI)",Favs!$AE$91,IF($D3="AC, AL & AM>= 60%",Favs!$AH$55,IF($D3="Adj Def. (AJ)",Favs!$AH$91)))))))))))))))))))))))))))))))))
F3F3=Favs!$AH$48
F4F4=Favs!$AE$72
F5F5=Favs!M84
F6F6=Favs!P60
F7F7=Favs!$AH$72
F8F8=Favs!$AB$72
F9F9=Favs!$P$72
F10F10=Favs!$Y$48
F11F11=Favs!AB60
F12F12=Favs!$Y$84
F13F13=Favs!AH64
F14F14=Favs!$S$72
F15F15=Favs!$P$48
F16F16=Favs!$S$48
F17F17=Favs!$V$48
F18F18=Favs!$AB$48
F19F19=Favs!AE48
F20F20=Favs!$M$72
F21F21=Favs!$M$48
F22F22=Favs!$M$60
F23F23=Favs!$S$60
F24:F27F24=Favs!AH76
C27,C3:C25C3=RANK(E3,Rank)
Click to expand...
I found a solution with the following vba code:
VBA Code: 
Range("C3:C27").SpecialCells(xlFormulas, xlNumbers).Offset(, -2).Resize(, 6).Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
305
gtd526
gtd526
gtd526
  • Question Question
convert cell value to formula
Replies
8
Views
360
Fluff
Fluff
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
363
gtd526
gtd526
gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
329
gtd526
gtd526
gtd526
  • Solved
vba replace the first 3 characters - (NBA indicators)
Replies
1
Views
464
gtd526
gtd526

Forum statistics

Threads
1,223,872
Messages
6,175,102
Members
452,613
Latest member
amorehouse

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top