'Automatically remove from drop down list in google sheets
Say that I have a dropdown list based on a column that has "cat, dog and rat" on it, and some lines select the option rat on the dropdown. After that I remove "rat" from the column (so form the dropdown), but the lines that had "rat" on it still with it and a msg saying that this data is invalid.
Is there a way to automatically remove the selected item that is not at the dropdown anymore? (put it blank maybe)
Check this image... "LEOBAZEREDO" is not on the list anymore, but his name is there with an alert. I would like that at this line the dropdown become blank when I remove LEOBAZEREDO from the itens
Solution 1:[1]
I just figured this out hope it helps someone:
I made the drop-down list using range (i5:i16) then I used the following formula in each cell in that range:
=if((countif($B$5:$G$5,"Jan"))>0,"","Jan")
changing the month accordingly.
this removes the text from the cell ounce it is used. Just to break it down a little....the countif portion (countif($B$5:$G$5,"Jan") will return "1" if it finds Jan in the range $B$5:$G$5 since this is bigger than 0 the if-function will put ""(no text)in the cell if it doesn't find "Jan" then it returns a 0 and the if-functions ads "Jan" to the cell.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |