VBA with Combo Boxes

[ISUCyclones]

I have a workbook with a list of 2 different team names followed by their rosters for each team (i will generally have more teams, for this example there is only 2).
On another worksheet, I want to be able to enter one of those teams' name into cell "F5" and then have a combo boxes that displays the names on the roster of that team within the combo box.

See my attached example:
In cell F5 of the first worksheet, when I type Lakers as the team name, I want the combo box in cell J9 to now display the list of names found under "Lakers" on my second worksheet (cells A3:A10).

Similarly, if I type in Thunder in cell F27, I want the combo box in J31 to now display the list of names found under Thunder on my second worksheet (cells A26:A32)

Is there a somewhat easy code/way to make this happen without me having to manually do this all the time?
If you have questions, please let me know.
Thanks

[jeffreybrown]

IMO, there is a better solution than comboboxes and VBA.

Look at the attached example which uses two dynamic named ranges and data validation. Yellow cells.

[protonLeah]

The attachment uses cells in column Z to build the player dropdown list. The comboboxes are replaced with data validation for each team. To add a team, just enter the team name and members on the player sheet. (sorry for modifying your format, but it was kind of hard on my eyes )

[ISUCyclones]

protonLeah,
Your format is what I'm looking for, however all my team names and players are all listed in one column (A). Is there a way for the data validation to work that way?
Thanks!

[protonLeah]

Data Validation for the team name cell needs a list of names. There is no validation formula that will parse column A for team names and build the list especially since they are only indicated by bold text. Same for the last member of the team. Therefore, since a macro is required in any case, the macro below will move the the teams to their own column (based on team names in bold text). I have changed the formulas on the Scorecard sheet to avoid #Ref! errors after the macro runs. This worked on the small list posted.

Please Login or Register  to view this content.

[ISUCyclones]

protonLeah,
This works great! It is doing everything that I hoped it would do. I appreciate all your help with the code for this.
THANKS!