Excel Auto-Adjusting Drop-Down List

In the image below we have a list of 4 names in column A.

We need to allocate them to one of the four states in column F. There are drop-down lists in the yellow cells – see image below.

As you select a name for one state that name is removed from the drop-down lists for the next states.

Column C handles the list. The formula in cell C2 is.

=FILTER(A2:A5,COUNTIF(F2:F5,A2:A5)=0,”None”)

The COUNTIF function counts how many times each name from column A appears in column F. If it equals zero it means the name isn’t in column F. Only names that aren’t in column F are displayed in column C.

Column C is used to populate the drop-down list – see the Date Validation dialog for the yellow cell in the image below.

The FILTER function in cell C2 is returning a spill range. Using the # symbol after the cell reference refers to the spill range.

As names are added to column F they are remove from column C – see image below.

When all the names have been selected in column F, the word None appears in cell C2.