i'm trying to create a dropdown list that return the values of a big table if a certain criteria is true:

For example, in this example I need the dropdown to list all the names which city is "Bilbao".

If I create a sheet that "lists" all the posible dropdowns (one for each city with the names listed below), there's no problem. But I'm trying to don't use that sheet, I mean, that the dropdown directly calculates the correct list as an array and show it. That's because the real workbook has more than 100 "cities" that can change at any time.

Is it possible or maybe i'm overcomplicating that?

Regards!

CodePudding user response：

You'll need to use a range that your filtered list of values appear in.

The formula =FILTER(Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11=Sheet1!$D$2) will return just those names that appear in the city shown in D2.
To use it in your data validation add a named range to the Name Manager (Formulas > Name Manager). My formula is in cell G2, so my named range looks at =Sheet1!$G$2# and is named CityFilter.

Now create your Data Validation to look at CityFilter.