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.

You can make a drop down list by using Data validation

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:

the drop-down list only includes the “filtered” column’s values.

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.

Google Sheets shows an error

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😢

No responses yet

Leave a Reply

Your email address will not be published.