Data Validation allowing each option to be chosen only once?

Hello, new user here.

I am wondering if there is a way to expand the data validation I am already using so that in the column which it applies to, each option given on the list of data can only be chosen once.

In practice this would mean that if I had 10 cells in the column and the data validation list giving options 1 through to 10, each number could only be chosen in one cell, and as you entered the numbers in the cells, your options would deplete until you had none left.

(To put this into context, I am using excel to allocate seats in a theatre and want to make sure that the same seat cannot be allocated more than once. If there is a more logical way to do this then any suggestions gratefully accepted!)

Thanks for reading.

Hi, one way is to automatically remove previously chosen values from the validation list (see Attached file).

Thank you, this is exactly what I want it to do.
However...when I try to change the options from 1 - 10 to what the options actually are (STALLS A12 - STALLS A23), the options all disappear... any ideas?

Yes, the reason is that the previous solution used rank, and this can't be done directly on text values. I have made a quick fix where I extracted the numbers 12-23 from the text values.

Thank you so much for taking the time to help me. I wonder if you could explain to me how to extract all the possible options from the text values, as I need to then do the same thing for all the seat numbers in the theatre (a lot!)

This is very helpful, thanks again!

Thanks for chippin in, Tony.

soprano_lou: Thanks for the feedback! I have mede a few changes to allow for all kinds of string values.
You could look into for example named ranges to make the solution more elegant, but it seems to work.