I need to create sports picking list over four terms. An offsite sport can only be picked once over the four terms. Therefore the list should change accordingly. e.g. if swimming has been chosen for term 2 then rowing, tennis or squash should not appear for the other terms. The options are:

Term 1 - Football, Fitness, Badminton, Rugby, Goals, Rowing (offsite)

Term 2 - Football, Fitness, Badminton, Rugby, Goals, Swimming (offsite)

Term 3 - Football, Fitness, Badminton, Rugby, Goals, Tennis (offsite)

Term 4 - Football, Fitness, Badminton, Rugby, Goals, Squash (offsite)How would I go about this? My excel skills are fairly basic and I need things to be broken down in plain English. eeek!

You should be able to use the technique used in this sample file:

DV0051 - Assign Players Each Inningwhich can be found at:

http://www.contextures.com/excelfil...

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.

Thank you very much... I have something going... I need to refine it! Your answer was very concise.

Ask Your Question

Weekly Poll