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.
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😢