Diminishing List Based on Previous Choices

It has been awhile since I have been here, good to see some of the regulars are still around.

I'm stuck on an NFL pool and hoping some of the experts can assist. In column W I want to select a player and in column X I want to see a list of teams they have left based on what they have previously chose. In the example attached it would be based on Jason and his choices in row 2. So in the example Jason can only choice from those listed as he can only pick a team once. In other words column X is column AB minus row 2. Would be nice to get this automated.

I appreciate the assistance.

D

Please see the updated sample file.

The second sheet, "how it works" shows the formulae used to create the diminishing lists. These columns can be hidden, as seen in the first sheet.

You also need to look at the Data Validation rules to see how the lists are picked.

Formula in AA2, copied down:

Formula:

Please Login or Register  to view this content.

Data Validation in cell B2, copied across and down:

Formula:

Please Login or Register  to view this content.

If you add players in column A, you will need to copy the formula down in column AA.

The diminishing Data Validation Lists could be on a separate worksheet, if you prefer, and that worksheet could be hidden. That would keep the live worksheet a bit tidier and more secure.

Here's the sample file with the DV Lists on a separate worksheet. That can also be hidden, if you wish.

You're welcome. Thanks for the rep.