Sorting with name ranges and indirect

Originally Posted by subzero42

. . . I think the picture and the file is pretty clear and visual to understand my need. . . .

You would be wrong, which is likely why no one else has responded before this.

Looks like you want to be able to change the membership of the teams in your 'database' in K24:L42 by changing Team entries in L25:L42, then have that flow into the table in W23:Y42. Note: that should be W24:Y42 because there are 18 original records, and row 25 to 42 spans 18 rows, and adding a row of column labels at the top means the table could begin in row 24.

If so, then the task is populating W25:Y42 from K25:L42. Not that difficult.

Formula:

W25:  =IF(ROWS(W$25:W25)<=COUNTIF($L$25:$L$42,W$24),INDEX($K$25:$K$42,MATCH(ROWS(W$25:W25),

         INDEX(MMULT(--(ROW($L$25:$L$42)>=TRANSPOSE(ROW($L$25:$L$42))),--($L$25:$L$42=W$24)),0),0)),"")

Copy W25 and paste into W25:Y42.

This is rather inefficient using no supporting formulas. Better to use some. For example, using N25:N42 and W21:Y22,

N25: =LOOKUP(L25,{"Blue",100;"Green",300;"Red",200})+COUNTIF(L$25:L25,L25)

Fill N25 down into N26:N42,

W21: =COUNTIF($L$25:$L$42,W24)
W22: =LOOKUP(W24,{"Blue",100;"Green",300;"Red",200})

Select W21:W22 and right into X21:Y22. Then you could use simpler and more efficient formulas like

W25: =IF(ROWS(W$25:W25)<=W$21,INDEX($K$25:$K$42,MATCH(W$22+ROWS(W$25:W25),$N$25:$N$42,0)),"")

Copy W25 and paste into W25:Y42.