vba selecting a specific range if ISNUMBER

gtd526 · Jan 28, 2024

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

A

B

C

D

E

F

2

As FAV

Rank

Criteria2

Criteria2 Percentage

Count

3

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($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%"

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%"

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"

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"

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%"

5

MOV2,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%"

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"

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"

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%"

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"

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"

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"

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"

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"

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"

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"

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%"

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"

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"

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"

,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"

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"

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"

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!

Misc

FALSE

1

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"

Indicators

Cell Formulas
Range	Formula
D3:D27	D3	=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
E3:E27	E3	=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)))))))))))))))))))))))))))))))))
F3	F3	=Favs!$AH$48
F4	F4	=Favs!$AE$72
F5	F5	=Favs!M84
F6	F6	=Favs!P60
F7	F7	=Favs!$AH$72
F8	F8	=Favs!$AB$72
F9	F9	=Favs!$P$72
F10	F10	=Favs!$Y$48
F11	F11	=Favs!AB60
F12	F12	=Favs!$Y$84
F13	F13	=Favs!AH64
F14	F14	=Favs!$S$72
F15	F15	=Favs!$P$48
F16	F16	=Favs!$S$48
F17	F17	=Favs!$V$48
F18	F18	=Favs!$AB$48
F19	F19	=Favs!AE48
F20	F20	=Favs!$M$72
F21	F21	=Favs!$M$48
F22	F22	=Favs!$M$60
F23	F23	=Favs!$S$60
F24:F27	F24	=Favs!AH76
C27,C3:C25	C3	=RANK(E3,Rank)

gtd526 · Jan 28, 2024

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
A B C D E F
2 As FAV As FAV Rank Criteria2 Criteria2 Percentage Count
3 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($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%" 1 Rank2, 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%" 2 MOV2, 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" 3 Q, R, S, T>0, W 61.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" 4 MOV2 > ATS 59.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%" 5 MOV2,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%" 5 MOV, 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" 7 K,L,Q,R>0,S>0,T>0 58.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" 8 Orange - Q,S,V,X,AA 57.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%" 8 Rank2, 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" 10 M,N>0, Q,R>0 55.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" 11 Asst/Tover >=1.85 55.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" 12 Q>=60%, S>=6 54.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" 13 Green - Q,S 53.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" 13 Aqua - L,Q,S 53.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" 15 Purple - Q,S,X 53.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" 16 K,L,Q,R>0,S>0,T>0,AC 50.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%" 17 Cover2% > 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" 18 K,L,Q,R>0,X 48.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" 19 Navy 47.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" 19 MOV2, MOV > ATS 47.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" 21 K,L,Q,R>0,X,AC 39.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/A Misc FALSE 1
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/A Misc FALSE Higher
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! Misc FALSE 1
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/A Misc FALSE 1
Indicators
Cell Formulas
Range Formula
D3:D27 D3 =MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
E3:E27 E3 =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)))))))))))))))))))))))))))))))))
F3 F3 =Favs!$AH$48
F4 F4 =Favs!$AE$72
F5 F5 =Favs!M84
F6 F6 =Favs!P60
F7 F7 =Favs!$AH$72
F8 F8 =Favs!$AB$72
F9 F9 =Favs!$P$72
F10 F10 =Favs!$Y$48
F11 F11 =Favs!AB60
F12 F12 =Favs!$Y$84
F13 F13 =Favs!AH64
F14 F14 =Favs!$S$72
F15 F15 =Favs!$P$48
F16 F16 =Favs!$S$48
F17 F17 =Favs!$V$48
F18 F18 =Favs!$AB$48
F19 F19 =Favs!AE48
F20 F20 =Favs!$M$72
F21 F21 =Favs!$M$48
F22 F22 =Favs!$M$60
F23 F23 =Favs!$S$60
F24:F27 F24 =Favs!AH76
C27,C3:C25 C3 =RANK(E3,Rank)

I found a solution with the following vba code:

VBA Code:

Range("C3:C27").SpecialCells(xlFormulas, xlNumbers).Offset(, -2).Resize(, 6).Select

vba selecting a specific range if ISNUMBER

gtd526

Well-known Member

Excel Facts

gtd526

Well-known Member

Similar threads

Forum statistics

Share this page

vba selecting a specific range if ISNUMBER

gtd526

Well-known Member

Excel Facts

gtd526

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock