Auto-Filters with Criteria Ranges Stored in Rows

[Saighead]

Hi,

How can I create and save multiple auto-filters for Sheet1!A:A (contains a LOT of non-unique text values) using data stored in rows on Sheet2 as criteria ranges (a separate filter using each row, that is)?

PS: Also posted here.

 

[Fluff]

Even more importantly, I need to be able to filter the results again using auto-filters on other columns

Where was this ever mentioned?

With the very limited example you showed (and the fact that you omitted some important information), it is highly unlikely that you are going to get a macro that will do every thing that you want, as there is just nowhere near enough information.

 

[Saighead]

Where was this ever mentioned?

The title says "Auto-Filters". They don't mess with the ability to apply additional filters later if needed. So, I didn't realize I had to be more explicit. My bad, I guess.

it is highly unlikely that you are going to get a macro that will do every thing that you want, as there is just nowhere near enough information.

Basically, all I'm looking for is a fast and non-destructive workaround for this pathetic two-criteria limit on filtering in Excel. Unless you want to tango with VBA and macros.
The solution I'm using now (for want of a better one) is a number of VBA AutoFilter scripts linked to values in the drop-down list. My biggest beef with it is that it destroys the Undo stack which I REALLY need intact...

 

[Fluff]

The two criteria limit does not exist if you are filtering on exact values, which you are.
If you want to keep the undo stack, then you filter manually, otherwise use VBA & loose the undo stack.
Your choice.

 

[Saighead]

Your choice.

Yeah, that's what I thought. Some choice. Would-you-prefer-to-be-buried-alive-or-burned-at-the-stake kind of thing...
34 freaking years on the market and Excel still sucks.

 

[Fluff]

If you don't like, use something else.

 

[GlennUK]

The solutions are only clunky because of the restrictions you gave in your requirements. Here is a solution that is robust, easy to implement, fast ... having an extra column on your data:

 

[Saighead]

That extra column was the first thing I thought of when looking for a way out of this mess. But the file is HUGE as it is and the number of columns is mind-boggling. So, no. Not robust. Ah well, it's just one of those things, I guess. Always thought Excel should never have made it into the big leagues. Falls on its face way too often, practically non-stop. Just one more in a LONG string of situations confirming I was not wrong...

No reflection on you whatsoever. Thanks a lot for your time, really appreciate your help.

 

[GlennUK]

The size of the file and number of columns has NOTHING to do with my solution, or its robustness ... it does exactly what you want, using native filtering, preserving the Undo stack. You moan like someone who really doesn't know Excel, but would like it do some things precisely as you imagine, then complain when Excel does it a different, but valid, way. You also judge that Excel should never have made it into the big leagues ... how do you think that happened ... do you think that someone just decided: "oh, we'll have Excel in the big leagues" ... no, it happened only because so many people could see the advantages of using something so flexible, and powerful. Yes, I said "powerful" ... do you think that people like myself or Fluff haven't used enormous files in our careers? The last really big thing I did was achieved in Excel using PowerPivot ... a truly staggering step forward, if you are willing to explore it.