Countif down to last row

[thedeadzeds]

Hi Guys,

Is there a way to change the formula so that this part '=INT(COUNTIF($K$2:$K$2000" copys down to the last row in column K. So the $K$2000 part. At the moment i have to change this manually

[TABLE="width: 121"] <tbody>[TR] [TD="class: xl64, width: 121"] =IF(COUNTIF(K$2:K2,K2)<=INT(COUNTIF($K$2:$K$2000,K2)/COUNTA(Blue_Team_Names[Blue]))*COUNTA(Blue_Team_Names[Blue]),INDEX(Blue_Team_Names[Blue],MOD(COUNTIF(K$2:K2,K2)-1,COUNTA(Blue_Team_Names[Blue]))+1),"Not allocated")[/TD] [/TR] </tbody>[/TABLE]

<tbody> </tbody>

 

[arthurbr]

Replace K2000 with INDEX(K:K,MATCH(9.99999999999999E307,K:K)) if the col contains numbers or INDEX(K:K,MATCH(REPT("Z",255),K:K)) for text

 

[Peter_SSs]

If you are changing that value because the data might go to row 2,000 or 1,800 or 2,500, could you just change it once to, for example, say 10,000 or some other figure that will always be bigger than your data?