Okay, perhaps the simplest way to achieve this is to add a helper table.
I have added this to your sheet "Player Data" Columns C:AL and used a grouping button to hide the table.
1/. In C4
Drag Across to Column AL then down to suit.
2/. In Sheet "Plays for"
In Column A I have put index numbers they are not really required but do serve as a visual indicator as to the row where the following formula ends
In B2
Drag Across to Column AE then down to the last index number.
If you don't want this column then use this formula instead
"Players" is a dynamic named range
Refers to:=
3/. As you will have some 30 names to add this code will do it for you.
4/. Unfortunatly Data Validation using =INDIRECT() doesn't like refering to dynamic names so we will need to use the Worksheet change event to change the lists as required
In the worhsheet module for Sheet "TR 1"
Check this out by selecting a name in Sheet "TR 1" and see the validation lists in C3:18
5/. Regarding the concatenation problem.
As we need VBa to get your drop-downs we may as well use a UDF to do the concatination.
Then in Sheet "Player Skills" B3
Drag/Fill Down as required
Note
I have removed the commas from your data and used ", " as the separator in the above UDF
The separator is optional if omitted it will use " " (space).
Use whatever you want as a separator e.g. " & " or " and " etc.
Lastly I have added some named ranges to make life a little easier
TeamNames
Refers to:=
If you have more teams to add put them in Sheet 'Player Data' BQ:BU
In Sheet "Rerolls" A1
Drag/Fill Down as required
This is now used to get the named list "Team"
Hope this helps
Bookmarks