Countdown (dynamic) drop down list

[Andy_C]

I am trying to create a drop down list that dynamically excludes values that have already been selected. Best explained in the simplified example below. Not sure if there’s a built in function to do this, or if I need a script and/or scratch sheet.

Ex: Resource allocation.
I have workers Tom, Dick, and Harry, and jobs Cashier, Cook, and Cleaner. I want the drop down to show all 3 workers if none are assigned to a job. If Tom gets assigned to Cleaner, I want the drop down for the other jobs to not show Tom’s name.

Appreciate any tips and tricks you guys can share. Thanks!

[Zizi64]

And how you want to reset this settings? (How you will modify a job-worker pair when the list will not contain all of the items???)

You can do is by some macro codes, and so many source lists for the dropdowns (depended on the number of workers and jobs).

[JohnSUN]

You will need a formula similar to
IF(IFERROR(MATCH(<list_of_names>;<$start_list>:<current_cell>;0);0);"";<list_of_names>)

The video uses addresses $A$2:$A$4 instead of <list_of_names> and $E$4:$E4 instead of <$start_list>:<current_cell>

Since there is no way to automatically remove duplicate values after selection from a drop-down list, you can optionally use Conditional Formatting to highlight erroneous values.

[Andy_C]

Thank you! This is exactly what I was trying to accomplish. I already have conditional formatting to highlight duplicates. My real pick list is much longer, and this makes it simpler to avoid getting duplicates in the first place.