HELP: ADVANCED FILTER WITH CUSTOM ViEW ISSUE

Hi DABHand and welcome to the forum,

My first thought is that your trying to filter a table using a value that no longer appears. Say you have a filter for "Bob" in the name column, but you have already filtered the data for "Female". Bob has been excluded from your data so he doesn't appear. When you try to set a filter on something that isn't in the data you will get an error message.

See if that makes sense. If not then keep asking.

Hi Marvin,

Thank you for taking a look at this.

Unfortunately I don't think that is the solution. One of the views I have saved is called "ALL DATA" which returns the sheet to an unfiltered state. Changing to the view that was created using the advanced filter from this state returns the same error.

All of my other custom views allow me to switch to and from each one without issue. It's just the one that involves an advanced filter that is not working. I can manually run the filter without issue, just not the custom view.

I've attached the little test file I setup. It has two custom views: "ALL DATA" to remove the filter and "FILTER" to apply the filter data on sheet2 to the data on sheet1 (returns 3 columns). I should add that the same thing occurs if the filter data is on the same sheet list data.

Thanks again and looking forward to your further insight.

Hi DABHand,

When I've done an Advanced Filter and used that filtered data in a chart or pivot, I don't do the filter in place. I'd always do it to a column or two to the right of the original data. Then I'd create a Dynamic Named Range of the filtered data and use this name for the data in my dashboard (chart or pivot). I looked at your example and it looked like you filtered in place which then left missing row numbers. If you filter to another range, it will keep all rows visible. See a simple example of one I've done in the past. It uses a Validation List based on the DNR but I think you will want a chart or pivot based on the created DNR.

Dynamic Dropdown List DNR Adv Filter On Change.xlsm