excel - Define a data validation list using FILTER or similar formula

Question

In cell A2, I have the following formula:

=FILTER(Projetos[Title];Projetos[CategoriaSubcategoria]=A1;"")

This returns:

question from:https://stackoverflow.com/questions/65925953/define-a-data-validation-list-using-filter-or-similar-formula

Answer 1

Put your filter formula in a helper range.

Then use a Named range with the formula:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzz",Sheet1!$A:$A))

And then use that name in the Data Validation.