|You should be able to use the technique used in this sample file:|
DV0051 - Assign Players Each Inning
which can be found at:
As each player is chosen at a position in an inning, the name is removed from the list for any other position in that inning.
For your use, the following steps will get you started. Once you have it working, and understand how it works, you can set up the spreadsheet however you like.
Basically, you are only going to be concerned with the "first inning". The rest of the innings won't matter.
1 - Substitute Term 1, Term 2, Term 3, Term 4 for the first four "Positions" in Column A.
2 - Replace the 9 Names in Column L with your 9 Sports.
3 - M4:M12 contain formulas. You want to keep the formulas in M9:M12. (for your offsite sports) but you want to replace the formulas in M4:M8 with the hard coded text of your sports so that they match the sports in Column L. It is the formula that removes the chosen sport from the drop downs, so you don't want the formulas for the sports that can be chosen in multiple terms (M4:M8).
Once you've completed those steps, you should get what you want in Column B: Chose an offsite sport in any term and it won't be available in the drop down for any other term - unless it is deleted from the Term it was chosen in.
After you've tried that you should explore the sheet (including the Data Validation rule) to see how it works. Once you've figured it out, you should be able to change the sheet to match your needs more precisely.
Come on back if you have any questions.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.