Data Validation Issue

[ISUCyclones]

I recently had a forum user help me with the attached file regarding data validation and a macro that allows me to pick from a list of teams and then it displays their roster in the validation boxes below. It works great for the first 26 teams I have listed, but after that, it no longer keeps the rosters with the teams. I was hoping someone could find the small glitch that will allow this process to work for all teams listed.
Thanks

[JBeaucaire]

1) on the PlayersSheet, create a named range (CTRL-F3) called Teams with this formula for the RefersTo:
=OFFSET(PlaysheetsBasketball12!$A$1, , , 1, COUNTA(PlaysheetsBasketball12!$1:$1))

This creates a self-expanding list of sheetnames for use on the other sheet.

2) Change the Data Validation in F6 to:
=Teams

3) Now use this formula for Data Validation in J9:
=OFFSET(PlaysheetsBasketball12!$A$1, 1, MATCH($F$6, Teams, 0)-1, COUNTA(OFFSET(PlaysheetsBasketball12!$A$1, , MATCH($F$6, Teams, 0)-1, 50, 1))-1, 1)

4) Copy that cell down the first section.

5) For the next section, just change the $F$6 to $F$27
...etc


Now you don't need all that other stuff on the right. Also, this will expand itself as you add columns to the PlayerSheet

[ISUCyclones]

JBeaucaire,
Thanks for your help. This looks to solve most of the problem. However, when I copy the data validation down into the rest of the cells, as I go down the cells, I lose 1 name per cell that I go down. So the last cell spot doesn't display any names (the first displays 10, next cell displays 9, next displays 8, etc.). I need all the names to show up in each of those cells' list. What adjustment needs to be made for that?
THANKS

[JBeaucaire]

I fixed the formula in step #3. Try it again.

[ISUCyclones]

Perfect. I appreciate you help!

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

[JBeaucaire]

duplicate... deleted.