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.

 

[GlennUK]

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]

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]

Set up these names and formulas, and drop-down ... and use Data/Advanced Filter/In Place with Criteria of MyCriteria.

 

[GlennUK]

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]

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]

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.

 

[Fluff]

Another option using advanced filter
With E1:E2 as the criteria range.

Dummy.xlsx
	A	B	C	D	E
1	Country	Product		Central America
2	Canada	Item 1			FALSE
3	China	Item 1
4	China	Item 2
5	China	Item 3
6	Denmark	Item 1
7	Greece	Item 1
8	Hungary	Item 1
9	India	Item 1
10	Japan	Item 1
11	Japan	Item 2
12	Mexico	Item 1
13	Norway	Item 1
14	Peru	Item 1
15	Peru	Item 2
16	Sweden	Item 1
17	Sweden	Item 2
18
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ISNUMBER(MATCH($A2,INDEX(Sheet2!$B$1:$O$10,MATCH($D$1,Sheet2!$A$1:$A$5,0),),0))

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=Sheet2!$A$1:$A$5

Dummy.xlsx
	A	B	C	D	E
1	Country	Product		Central America
12	Mexico	Item 1
18
19
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=Sheet2!$A$1:$A$5

 

[GlennUK]

Ooh, Fluff, I wish I'd have remembered that method ... very nice.

 

[Saighead]

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.