Populate roster in a scoresheet

Take a look at this:
http://www.contextures.com/xlDataVal02.html

Here is an idea that you can expand upon. It uses names for the team ranges then uses those names to fill in the roster column dependent upon the teams chosen as HOME or AWAY.

Insert the rest of your teams by replacing the dummy names that I have created. Replace the numbers under each team name with the name of the players. All the changes will automatically work. Delete any numbers not used in each team's list.

This worksheet depends very much on Named Ranges. Each team range has the name of the team and all the teams selected in the first row of the second sheet form a list called TEAMS.

Data Validation for the Visiting and Home teams use the named list for the TEAM names. When a team is selected from the drop-down list that supplies the Array Formula in the roster list with the variable (Team name) from which it draws the list of players. That formula is created by selecting the whole range where the players' names appear and entering the ARRAY FORMULA that you will see if you click in the roster range.

That is about all there is to it.

Discovered a problem with the worksheet. Will get back to you.

When you change the name of one of the numbered teams, go to the Formulas tab, Name Manager, click on the Team number that you changed the name of and click on Edit. Enter the new name.

If you have need of team names that are more than one word, let me know.

This version will allow you to enter the team names even if they are more than one word without having to go into the Name Manager to make changes. Name the teams whatever you like. Leave the gray shaded area alone as it takes care of filling in the drop-down lists and team roster presentations.

You're welcome. Thanks for the feedback.

Have a wonderful day in the northwest!