Search code examples
validationgoogle-sheetsfilterdropdowntranspose

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

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

Solution

  • 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<>""))

    enter image description here

    then hide columns and use validation:

    enter image description here

    where this is the result:

    enter image description here

    demo sheet

    update:

    1st fx:

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

    2nd and every next fx:

    =TRANSPOSE(FILTER(A$2:A, NOT(COUNTIF({
 INDIRECT("C2:C"&ROW()-1); INDIRECT("C"&ROW()+1&":C")}, A$2:A)), A$2:A<>""))

    enter image description here