Data Validation Dropdown With Filtered Data Range

3
votes

I'd like to use data in column D
For a drop down in another sheet (data validation)
Only items where column A is less than 9

I tried List from a range
But it looks like you cannot put a formula in the range box
e.g.

=FILTER(GrpsTY!D2:D, GrpsTY!A2:A < 9)

Any suggestions?

enter image description here

enter image description here

2
validationgoogle-sheetsfilterdropdowngoogle-sheets-formula
I assume you were looking to avoid using a helper column? Otherwise creating your filtered list of values, possibly placed on another sheet, and pointing to that list for your data validation, would achieve your objective, wouldn't it? - kirkg13
@kirkg13 Yes, I could do that. But I'm trying to avoid things like that if possible. Thanks. - Mathias

2 Answers

1
votes

formulas are not supported in data validation. the best course of action is to create a helper column with your filtered entries and then feed it to data validation.

1
votes

Maybe they have added the functionality since the most recent answer (last July, 2020)?

I'm trying to figure out a formula that will let me validate data using a filtered list, and stumbled onto this:

This image shows that Google Sheets supports data validation using a custom formula

For Criteria, select "Custom formula is" instead of the default, "List from a range". Hope this helps anyone who comes after me :)