Drop-down list that filters values (without having a separately filtered list)

As far as I know you have to create the filtered list first and reference it.

Thanks a lot for both replies!

I watched the explanation to extract unique items from a list, and it gave me a good overview of the functions I could use. However, it does not solve my problem directly.
The array function needs to be copied to as many cells as I expect unique values in my initial list. But I want the function to return a list of unique values in one cell, so to say. In addition,
what I forgot to mention in my original post, is that I also need the dates to be reformatted, so that only years are returned, and from the years I want the unique values.

I will play around a little with what I got from the explanation.

I also managed to get the latest functions (dynamic array functions) to work by using Excel online. However, the 'unique' function does not work in a drop-down list, it throws:
"The list source must be a delimited list, or a reference to single row or column.".

Maybe nick.williams is right, and there is no such option of having the drop-down dynamically extract the values on its own. Why is that? Is there a specific reason this is not working?
I guess the 'unique' function does not return a "delimited list", but what does it return?

Again, thanks a lot in advance
Thewald

Thanks a lot for the reply!

I tried to wrap the 'textjoin' function around the 'unique' function. The result put into a cell, in fact, looks like the required delimited list.
However, putting the formula as it is ( =TEXTJOIN(",";TRUE;UNIQUE(_named_range_)) ) into the data validation field does not yield a useful result.
The settings window for the data validation closes without error, however, the cell does not have a drop-down arrow next to it, and therefore no
options are visible. Furthermore, putting anything into that cell results in an error (I tried values, and also putting the formula into the cell,
to see if it interprets the formula as a string for some reason).

It seems that data validation does not accept the outcome of the 'textjoin' function as a valid list.

Any suggestions?

Thanks a lot in advance again!
Thewald

Thanks a lot then! I will go with the separate range on a hidden worksheet.
Best regards, Thewald

One thing that works fairly well with the 365 functions is that you can refer to array formulae that spill for the Data Validation list, so you don't get blanks or have to guess how many rows you need.

For example in your sample sheet, if you want a sorted unique list of the years, you could put =SORT(UNIQUE(YEAR(A2:A9))) in E2. This will return 2021, 2022, 2023 and 2024 in E2:E5. But for your data validation you can refer to E2# which will always give you the results of the formula in E2.

I implemented that, and it works. Thanks a lot!
Best regards, Thewald