Drop Down List - No.1 to No.10, non-repeating

[tlc53]

Hi there,

I would like to have a drop down list with options No.1 to No.10 but once a number has been used, it is removed from the drop down list.

I have found one way to do it but I'm hoping there's a better way.

Drop down lists go in cells D180:D260.

Currently my data validation list is referring to column L which has an if statement returning No.? if column K = 0.

Column J ...........Column K ...............................................Column L
J184 = No.1 .......COUNTIF($D$180:$D$260,J184) = 1
J185 = No.2 .......COUNTIF($D$180:$D$260,J185) = 1
J186 = No.3 .......COUNTIF($D$180:$D$260,J186) = 1
J187 = No.4 .......COUNTIF($D$180:$D$260,J187) = 1
J188 = No.5 .......COUNTIF($D$180:$D$260,J188) = 0 .........No.5
J189 = No.6 .......COUNTIF($D$180:$D$260,J189) = 0 .........No.6
J190 = No.7 .......COUNTIF($D$180:$D$260,J190) = 0 .........No.7
J191 = No.8 .......COUNTIF($D$180:$D$260,J191) = 0 .........No.8
J192 = No.9 .......COUNTIF($D$180:$D$260,J192) = 0 .........No.9
J193 = No.10. ....COUNTIF($D$180:$D$260,J193) = 0 .........No.10

I would prefer not to have workings on my spreadsheet but rather have it all under the Data Validation section. Also, as above, where option No.1-No.4 are no longer available, it is showing as blank spaces (even though I have Ignore Blank ticked).

Can anyone please tell me if there's a better way to do this?

Thank you!

 

[Yongle]

This method may help https://trumpexcel.com/multiple-drop-down-lists-in-excel/

 

[tlc53]

Thank you for the link. I watched it and it was definitely helpful.
Before I go down this route, can you please confirm there's no way to do it by a formula in data validation. I would be happy with 1 - 10, rather than No.1 - No.10.

I have seen a post where the below formula was used to restrict entries in the range with 1 - 4 with no repeating numbers.

=AND(COUNTIF($A$1:$A$20,A2)=1,OR(A2=1,A2=2,A2=3,A2=4))

However, this only restricts data entry whereas I want available options to show in a drop down list.

Just thought I'd ask. I'm guessing it is probably unlikely. Thank you!