'Google Sheets List and Category
I have two ranges. The first range has a list of dogs, and the second range has a list of cats.
I created a dropdown list contains all Dogs and Cats. How Can I change dynamical the category? (Orange colour on screenshot) If someone chose Dog, the category will change to "Dogs". Same with cats.
Solution 1:[1]
You can use a custom formula for the dropdown:
=MATCH(B1, QUERY({C1:C8}, "select Col1 where Col1 contains '"®EXEXTRACT(A1, "(.+)s")&"'"), 0)
Where B1 is the cell that the dropdown is in, A1 is the cell where the user chooses from 'Cats' or 'Dogs', and C1:C8 is the list of possible values that needs to be filtered.
The spreadsheet when the value entered matches:
The spreadsheet when the value entered doesn't match:
If you want it as a dropdown list, you can filter the range of items and then reference that in the data validation:
=QUERY({C1:C8}, "select Col1 where Col1 contains '"®EXEXTRACT(A1, "(.+)s")&"'")
Solution 2:[2]
Try, if E6 for instance is the value choosen in the dropdown list
=REGEXEXTRACT(E6,"\w+")&"s"
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 | |
| Solution 2 | Mike Steelson |