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.

CodePudding user response：

You can use a custom formula for the dropdown:

=MATCH(B1, QUERY({C1:C8}, "select Col1 where Col1 contains '"&REGEXEXTRACT(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 '"&REGEXEXTRACT(A1, "(. )s")&"'")

CodePudding user response：

Try, if E6 for instance is the value choosen in the dropdown list

=REGEXEXTRACT(E6,"\w ")&"s"