I have a Google Sheets where I would like to have drop down menus in a column. When a choice is made in a drop down menu, I would like it to be no longer present in the others.

So I added a query function to subtract the values already selected. This works fine but I get an error message saying that the value is no longer present in the specific range. And this is quite normal as I am feeding the drop down menus with the data from the query function. And when I select a value in my dropdown, it disappears from the column where the query function is located.

So I would like to know if there is an alternative to not have the error message (either hide it or use another function).

Here is my sheet before filling in my drop-down menu :

And here it is after :

Here is my function: =query(query({A2:A;C2:C}; "Select Col1, count(Col1) where Col1<>'' group by Col1"); "Select Col1 where Col2=1")

And here is the link to my Sheets if you want to check it out for yourself. Thanks in advance for your help

CodePudding user response：

Use a dedicated range for defining the available values per each drop-down separately. Insert columns to the right and use these formulas:

cell D2: =transpose(A2:A)

cell D3: =transpose( filter( A$2:A; isna(match(A$2:A; C$2:C2; 0)) ) )

Copy the formula in D3 down. Select cell C2, choose Data > Data validation and set the "List from a range" to =D2:N2. Save and reopen the dialog box to remove the dollar signs that appear by default.

Then copy cell C2 down.