Filtering a Range for Data Validation without Advanced Filter or Code?

[idlewild]

Hi All-

I'm a relative n00b so please go easy.

ISSUE SUMMARY:
In Excel 2010, I have a spreadsheet with two tabs: the first tab contains two columns: A which lists the names of individuals and B which lists whether or not they are available (a simple "Yes" or "No" data validated list). So something basic like:

A B
Bob Yes
Pete No
Jane No
Sue Yes

The second tab has a single column, C, against which I want to apply data validation to but only show those people that are available. In other words, I want to filter out those from the first tab's column A whose associated column B is "No." So, C's dropdown options using the sample set about would be "Bob" and "Sue" only.

QUESTION:
Is there a simple, eloquent, way to do this WITHOUT using VBA, advanced filtering, etc. even if I have to create a working data set elsewhere? It seems like I should be able to use a named range and filter it somehow in a formula applied to the data validated list, but I can't figure it out nor find any similar solution online.

Thank you in advance!

 

[markmzz]

Idlewild,

Try this for 2 worksheets Sheet1 (columns A-Name and B-Avaliable - range A1:B21) and Sheet2 (column C):

1) In Sheet1, activate an AutoFilter in A1:B21 (select cell A1/active tab Data/click Filter in the group Sort and Filter).

2) Filter the data in column B (Click the Arow in the cell B1/leave active only Yes and click OK).

3) Select with your mouse the filtered data (only the filtered data of the column A) and copy to the cell C1 (Sheet2).

Markmzz

 

[markmzz]

Idlewild,

Or Try this for 2 worksheets Sheet1 (columns A and B - range A1:B21) and Sheet2 (column C):

1) In the cell D1 of the Sheet1, type this formula (count the number of Yes):

=COUNTIF($B$2:$B$21,"Yes")

2) In the cell C1 of the Sheet2, type Avaliable.

3) In the cell C2 of the Sheet2, type this formula (array formula - so use Ctrl+Shift+Enter and not only Enter):

=IF(ROWS(C$2:C2)<=Sheet1!$D$1,INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$B$2:$B$21="Yes",ROW(Sheet1!$B$2:$B$21)-ROW(Sheet1!$B$2)+1),ROWS(C$2:C2))),"")

4) Now, copy the formula of the cell C2 of the Sheet2, down C3:C? until Excel show a blank cell.

Markmzz

 

[T. Valko]

Hi All-

I'm a relative n00b so please go easy.

ISSUE SUMMARY:
In Excel 2010, I have a spreadsheet with two tabs: the first tab contains two columns: A which lists the names of individuals and B which lists whether or not they are available (a simple "Yes" or "No" data validated list). So something basic like:

A B
Bob Yes
Pete No
Jane No
Sue Yes

The second tab has a single column, C, against which I want to apply data validation to but only show those people that are available. In other words, I want to filter out those from the first tab's column A whose associated column B is "No." So, C's dropdown options using the sample set about would be "Bob" and "Sue" only.

QUESTION:
Is there a simple, eloquent, way to do this WITHOUT using VBA, advanced filtering, etc. even if I have to create a working data set elsewhere? It seems like I should be able to use a named range and filter it somehow in a formula applied to the data validated list, but I can't figure it out nor find any similar solution online.

Thank you in advance!

You can create a "filtered" list to another location using formulas and then use this list as the source for your drop down.

Let's assume this is your data:

Book1
	A	B
1	Name	Status
2	Name1	No
3	Name2	Yes
4	Name3	No
5	Name4	Yes
6	Name5	No
7	Name6	Yes
8	Name7	Yes
9	Name8	No
10	Name9	Yes
11	Name10	Yes
12	Name11	Yes
13	Name12	No
14	Name13	No
15	Name14	Yes
Sheet1

In the formulas I use the following named ranges:

• Names
• Refers to: =Sheet1!$A:$A
• Status
• Refers to: =Sheet1!$B$2:$B$15

In the summary area:

Book1
	D	E
1	Count	Name
2	8	Name2
3	_	Name4
4	_	Name6
5	_	Name7
6	_	Name9
7	_	Name10
8	_	Name11
9	_	Name14
10	_	_
11	_	_
12	_	_
13	_	_
14	_	_
15	_	_
Sheet1

Enter this formula in D2. This will return the count of records that meet the criteria.

=COUNTIF(Status,"yes")

Enter this array formula** in E2. This will extract the names that meet the criteria.

=IF(ROWS(E$2:E2)>D$2,"",INDEX(Names,SMALL(IF(Status="yes",ROW(Status)),ROWS(E$2:E2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down until you get blanks.

Now, as the source for your drop down list use this formula:

=E2:INDEX(E2:E15,COUNTIF(E2:E15,"?*"))

 

[idlewild]

Excellent.

Thanks, Markmzz and Biff! I have it working now (with a couple different options)!

Cheers.

 

[T. Valko]

Excellent.

Thanks, Markmzz and Biff! I have it working now (with a couple different options)!

Cheers.

You're welcome. Thanks for the feedback!

 

[markmzz]

Excellent.

Thanks, Markmzz and Biff! I have it working now (with a couple different options)!

Cheers.

Idlewild,

Thanks for the feedback. And consider using the Advanced Filter and Filter (powerful resources of Excel) in the future.

Markmzz

 

[Qikslvr]

Idlewild,

Or Try this for 2 worksheets Sheet1 (columns A and B - range A1:B21) and Sheet2 (column C):

1) In the cell D1 of the Sheet1, type this formula (count the number of Yes):

=COUNTIF($B$2:$B$21,"Yes")

2) In the cell C1 of the Sheet2, type Avaliable.

3) In the cell C2 of the Sheet2, type this formula (array formula - so use Ctrl+Shift+Enter and not only Enter):

=IF(ROWS(C$2:C2)<=Sheet1!$D$1,INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$B$2:$B$21="Yes",ROW(Sheet1!$B$2:$B$21)-ROW(Sheet1!$B$2)+1),ROWS(C$2:C2))),"")

4) Now, copy the formula of the cell C2 of the Sheet2, down C3:C? until Excel show a blank cell.

Markmzz

Ok Markmzz, This works great for me as well, but I have 2 critera that I need to filter on. for example my list of names may include hundreds of names, a Yes/no column, and a "Certified" column. So I would need to list only those names that were available ("Yes") and certified ("Yes").

I tried to use the AND function within the IF statement, but that didnt work. what would you suggest to only list the the names meeting both requirements?

Thanks,

 

[T. Valko]

Ok Markmzz, This works great for me as well, but I have 2 critera that I need to filter on. for example my list of names may include hundreds of names, a Yes/no column, and a "Certified" column. So I would need to list only those names that were available ("Yes") and certified ("Yes").

I tried to use the AND function within the IF statement, but that didnt work. what would you suggest to only list the the names meeting both requirements?

Thanks,

What version of Excel are you using?

 

[Qikslvr]

Sorry, Im using XL 2007