Excel - Restrict Options in Drop Down Fields to those currently not in use

Question

I restrict the options (an ID) for column A:A to those given in a drop down menu.

The possible elements are in K1:K10. In L1:L10 there is a flag (0/1) whether the ID is currently already in use or not.

Now I want to restrict the options in the drop down menu to those from K1:K10 which are flagged as 0 in L1:L10. Those flags can change multiple over the life sycle of that document. While in use, I don't want that ID to be selectable. Ones no longer flagged as in use, I want it to become available again.

Answer 1

Depending on your Excel version this may be a solution, as Validation Lists can refer to dynamic arrays.

Use the FILTER function to create a dynamic array of valid names in cell M1.

=FILTER(K1:K10,NOT(L1:L10))

Then set the Validation List to =$M1$#. NB the # is important as it tells Excel you are using a dynamic range rather than a standard cell reference.

and now (if you have Calculation set to Automatic) you should see the list of available names change as you change 1's and 0's in Column L.