I’m working with a large dataset in Excel and need to create a dynamic data validation list that only shows unique values from a specific column. Additionally, I want this list to update automatically when the table is filtered.

I’ve tried using OFFSET and MATCH functions to create a dynamic range, but it doesn’t seem to account for filtering. Here is a simplified example of what I’m trying to achieve:

I have a table with columns Category, Product, and Sales.
I want to create a data validation list in another sheet that shows unique Product names based on the Category selected and updates automatically when I filter the table by Category.

=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B$2:$B$100), 1)

However, this doesn’t filter out duplicates or consider filtered results. How can I adjust this to meet my requirements?

Any insights or alternative approaches using advanced Excel functions or even VBA would be greatly appreciated!