Google Sheets - Removing the used items from drop-down lists-CodePudding

In Google Sheets I have a column containing a list of available Serial Numbers (say, column A). Somewhere else (say, column B) a user must choose the serial number used among those listed in column A; I use Data Validation with a drop-down list in order to prevent the user to use a non-existent serial number.

My goal is to allow the user only choose the remaining available serial numbers, by removing from the drop-down list all the serial numbers already used.

By using the FILTER function, combined with MATCH and ISNA, I am able to create a column of available serial numbers (say, column C). The function used is: =FILTER(A2:A;ISNA(MATCH(A2:A;B2:B;0))). Then I moved the Data Validation list of column B (where the user must select the serial number used) from column A (all serial numbers) to column C (filtered serial numbers). I also added the "Reject input" in the Data Validation form, so I can allow the user only to enter a value listed in column C.

It works, but all the previously entered serial numbers on column B have a small red triangle showing that data is not valid. Of course, this happens because all entered values are removed from the data validation list.

I could simply ignore the red triangles, but I don't like this solution that much, because it always looks like there's an error on the sheet, and when we will have many data inside it would be difficult to distinguish this problem from any others.

Is there a different way to solve?

Thanks

CodePudding user response：

with formula only you can use:

=TRANSPOSE(FILTER(A2:A, NOT(COUNTIF(C3:C4, A2:A)), A2:A<>""))

=TRANSPOSE(FILTER(A2:A, NOT(COUNTIF({C2; C4}, A2:A)), A2:A<>""))

=TRANSPOSE(FILTER(A2:A, NOT(COUNTIF(C2:C3, A2:A)), A2:A<>""))

then hide columns and use validation:

where this is the result: