Autofilter options to be displayed as dropdown on another sheet

[BuZZarD73]

I have a large data set on sheet 1 and which feeds some reports on sheet 2.

I want the user to be able to customise the reports by essentially filtering criteria on sheet 1 but without having actual access to it.

The idea is to be able to access the values of the autofilter in another sheet, so that if they select a value from say column a, the selections for column b will reduce (as it does at source), which thereby prevents them from making invalid selections as the criteria will reduce according to previous selections for other columns.

I currently have a unique filtered list from each column which I use to provide a dropdown (through the data validation option). This is ok, but the user can select multiple criteria which filters out all the data, giving no results. I have a message box informing them of this, however I'd like to prevent them from doing this instead of telling them afterwards by making such criteria unavailable.

So I suppose the question is how do I get the available autofilter criteria into a cell on a different sheet using data validation?

Or is their another way to achieve this?

[AlphaFrog]

You're describing a data table Slicer. That feature was added to Excel 2013 and later.

Beyond column filters: Slicers on tables

If you don't have access to Excel 2013, I suppose you could do it with VBA.

I currently have a unique filtered list from each column which I use to provide a dropdown (through the data validation option).

Could you to better describe exactly how you do this and a provide an example workbook.

[BuZZarD73]

Thanks for the response.
We don't yet use 2013 at work so I'll have to wait until around the year 2020 before that becomes available

I have found a work around which essentially achieves my aim, by immediately applying the user filter selections on the worksheet_change event, instead of waiting for all the selections to be made and then telling the user there's no data, without indicating which selection was responsible.

Quote Originally Posted by BuZZarD73 View Post
I currently have a unique filtered list from each column which I use to provide a dropdown (through the data validation option).
Could you to better describe exactly how you do this and a provide an example workbook.

From the main dataset (sheet 1), I copy the unique values from each column to sheet 3 as individual lists.
I then sort each list into alphabetical order and this provides the data validation lists for user selection on sheet 2.
The user selects their options from the validation lists displayed on the report generator (sheet 2) and this filters sheet 1 before running the report to pull various fields into a table/graphs etc.

Ideally, the options in sheet 2 would mirror the autofilter options on sheet1 directly, without having to go through list validation, however applying the filters on selection does achieve the result I was looking for.

Thanks.