Create in-cell dropdown with filtered range

Question

I'm using Google Spreadsheet.

To illustrate my problem, I use the range A2:A8 for the data validation of D2 and E2.

But because in cell D2, you are supposed to select an animal only, I'd like to filter the range with B2:B8.

What I've tried, is using my own formula which is :

=FILTER(A2:A8;IS("B2:B8";"ANIMAL")) 

but this won't work and I cannot pick the "dropdown" option if I use custom formula.

I've also tried my formula in my Range selection, but it's not valid. What is the right formula to use to have a dropdown with filtered data?

Any thoughts?

Answer 1

As it stands, in Google Sheets, the only way to natively (that is, without resorting to Google Apps Script) populate drop-down lists is to use a comma-separated list, or reference a range. So in your case you would need to reproduce your filtered list somewhere in the spreadsheet (could be on a hidden sheet):

=FILTER(A2:A8;B2:B8="ANIMAL")

and then reference the range of that output in Data validation.

The ability to use a formula to generate the drop-down list directly would be a powerful feature, and has been submitted as a feature request by many (you might like to do the same: Help menu, Report an issue).