Tips: How to create a drop-down list that excludes values that have already been entered

Google Sheets has a useful feature called “Data Validation” that limits user input to acceptable limits.

If you set its “Criteria” to “List from a range,” the target cells will show a drop-down list that lists up the values that are included in the range you set.

However, sometimes you might want to restrict the input value to be unique in a specific column. How can you make a drop-down list that only allows one unique selection per column?

Use QUERY function

You can make a column that contains only values that are not in the user’s input. To do so, you can use QUERY function. In the above example, you can write this formula:

=query(C4:C10,"select C where C<>'"&JOIN("' and C<>'",A4:A10)&"'"&"")

This means it shows the values from the range “C4:C10” that are not in the range “A3:A10”. By using JOIN function, the “where” clause is expanded like this:

where C<>A4 and C<>A5 and C<>A6 and ...

By setting this query’s result to the range of the drop-down list, the items in the drop-down list will be narrowed down to the range, like this:

So the user will be helped to avoid entering duplicate values.

I’ve made a sample spreadsheet for this. Please feel free to copy this sheet and try your own.

One problem

I’m broadly satisfied with the above result. However, there is one thing that I don’t like. The cells I input show a label that says “Invalid input.”

It is natural because when I input a something value, the value is removed from the filtered list, and according to its purpose, Google Sheets warns that the input is wrong.

I’ve looked for a workaround to hide this message, but I can’t find it…
If anyone knows how to hide this error, please let me know😢