Auto-Filters with Criteria Ranges Stored in Rows

Saighead

New Member
Joined
May 17, 2013
Messages
33
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Saighead

New Member
Joined
May 17, 2013
Messages
33
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you don't like, use something else.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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:
 

Attachments

  • FilterSnip3.JPG
    FilterSnip3.JPG
    84.6 KB · Views: 2

Saighead

New Member
Joined
May 17, 2013
Messages
33
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,018
Messages
5,628,166
Members
416,297
Latest member
Kara Payne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top