Drop down list - can you stop already selected items being reselected?

[Hattie]

Drop down list - can you stop already selected items being reselected?
So I can only select an item in the list once (grey-out or remove already selected items - or something like that)

I have created a drop down list of staff names. (Data > Data validation, selected List…) not a combo box listbox.
That works fine.

Once I have picked a member of staff in a cell and move to the next cell is there a way to “not allow” me to accidentally select the previously allocated staff member again?

Basically once I have assigned Alice to a particular workstation I don’t want to accidentally assign Alice again to another workstation, she can’t be in two places at once

I can’t find an option for this function, or is it going to need code to do this magic?

I tried searching for this but maybe i am not using the correct terminology!

thank you.

 

[Anthony47]

So you have a Validation list named for example LIST1 and you use it for validation in say column A
In a free column, let's suppose in N1, insert this formula

=UNIQUE(VSTACK(LIST1,A1:A100),,TRUE)

Now as soon you use a value, it will be removed from this new list
At this point you may change the validation rule: instead of using =LIST1 for the validation source you will use =N1#

 

[Hattie]

Thanks for that.

i will try it tomorrow when i get to the office.

 

[Hattie]

Thanks, it worked perfectly.

 

[Anthony47]

Thank you for the feedback