Create in-cell dropdown with filtered range

google-sheets formula spreadsheet
rayfranco picture rayfranco · Oct 9, 2014 · Viewed 30.5k times · Source

I'm using Google Spreadsheet.

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

enter image description here

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

AdamL picture AdamL · Oct 10, 2014

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).

Related questions

How to utilize date add function in Google spreadsheet?

I believe the issue I am having now should be much easier in MS Excel. However, since my company uses Google Spreadsheet so I have to figure out a way. Basically, I have a cell that contains a date value …

Read More
How to extract URL from Link in Google Sheets using a formula?

I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the …

Read More
The last & the first day of a month in Google Spreadsheets

What is the way to get the first and last day of the previous month in Google Spreadsheet? I tried several ways to solve this issue with no success.

Read More