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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
So, how do you want it to operate? You have your different filter sets in Sheet2 ... do you want to apply a particular filter at a particular time, or what?
 

Saighead

New Member
Joined
May 17, 2013
Messages
33
So, how do you want it to operate? You have your different filter sets in Sheet2 ... do you want to apply a particular filter at a particular time, or what?
I want to create a drop-down list populated with values of Sheet2!A:A. When a specific value is selected, values in the corresponding row should be used as a criteria-range for filtering. And if at all possible, this shouldn't mess with the Undo stack.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Set up these names and formulas, and drop-down ... and use Data/Advanced Filter/In Place with Criteria of MyCriteria.
 

Attachments

  • FilterSnip.JPG
    FilterSnip.JPG
    60.7 KB · Views: 9

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

Set up these names and formulas, and drop-down ... and use Data/Advanced Filter/In Place with Criteria of MyCriteria.
actually you don't even need the MyCriteria name, if you are keeping that in the same sheet as the list ... just use the full range of the criteria anyway.
 

Saighead

New Member
Joined
May 17, 2013
Messages
33
Set up these names and formulas, and drop-down ... and use Data/Advanced Filter/In Place with Criteria of MyCriteria.

Nope, that's not it. If filtered by "Central America", results should read: "Mexico | Item 1", not "Country", "Guatemala", and "Mexico" and there shouldn't be any residual junk like those 0's. Besides, rows on Sheet2 are VERY different in length, and I wouldn't want to slow filtering to a crawl by including thousands of empty cells in the criteria range.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

Yes, it is it. I've filtered by Central America .... here are the results. The dynamically defined name excludes the zeros, so go back, and read what I said ... properly. Besides, you have no idea how fast Advanced Filter is ... even if the zeroes were included, you would not be able to tell the different.
 

Attachments

  • FilterSnip2.JPG
    FilterSnip2.JPG
    19 KB · Views: 4

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Another option using advanced filter
With E1:E2 as the criteria range.

Dummy.xlsx
ABCDE
1CountryProductCentral America
2CanadaItem 1FALSE
3ChinaItem 1
4ChinaItem 2
5ChinaItem 3
6DenmarkItem 1
7GreeceItem 1
8HungaryItem 1
9IndiaItem 1
10JapanItem 1
11JapanItem 2
12MexicoItem 1
13NorwayItem 1
14PeruItem 1
15PeruItem 2
16SwedenItem 1
17SwedenItem 2
18
Sheet1
Cell Formulas
RangeFormula
E2E2=ISNUMBER(MATCH($A2,INDEX(Sheet2!$B$1:$O$10,MATCH($D$1,Sheet2!$A$1:$A$5,0),),0))
Cells with Data Validation
CellAllowCriteria
D1List=Sheet2!$A$1:$A$5


Dummy.xlsx
ABCDE
1CountryProductCentral America
12MexicoItem 1
18
19
Sheet1
Cells with Data Validation
CellAllowCriteria
D1List=Sheet2!$A$1:$A$5
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Ooh, Fluff, I wish I'd have remembered that method ... very nice.
 

Saighead

New Member
Joined
May 17, 2013
Messages
33
GlennUK, Fluff, thank you guys. Your solutions work but they are way too clunky. I need to do this kind of thing A LOT, and there's just too many steps before you get the results and there's a helper column in a worksheet that's already huge. I'm looking for a one- or two-click solutions and zero additional clutter. Even more importantly, I need to be able to filter the results again using auto-filters on other columns and with these advanced filter routines I don't see how I can. Anyway, thanks again for your time.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,595
Messages
5,625,697
Members
416,128
Latest member
WarJamAnd

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