=SORT(UNIQUE(FILTER

[FluffySlipper]

Hi,
I am trying to create a dynamic drop down. I am working on the second unique list and I am referencing a column in a table. There are at least 200 cells with data but the formula only returns zeros.

Formula I'm using is: =SORT(UNIQUE(FILTER(Accommodation[Country],Accommodation[Location]=$A$20,0)))
Accommodation = table name
Country = column 1
Location - column 2

If I remove the ,0 at the end I get the #calc! error.

Any help would be hugely appreciated.

 

[hagia_sofia]

Hello, could you please post a sample of the data?

 

[FluffySlipper]

No problem

 

[StephenCrump]

What is in A20?

It doesn't make sense to filter country by location - if you specify location = Hwange you'll always get Zimbabwe.

My guess is that A20 is Zimbabwe, and you actually want: FILTER(Accommodation[Location],Accommodation[Country]=$A$20)

 

[FluffySlipper]

What is in A20?

It doesn't make sense to filter country by location - if you specify location = Hwange you'll always get Zimbabwe.

My guess is that A20 is Zimbabwe, and you actually want: FILTER(Accommodation[Location],Accommodation[Country]=$A$20)

You, you're right the result in A20 is currently Zimbabwe. Thank you so much, swapping the country and location has made it work.