Data Validation List from Filtered Table

问题:

Below is the source data (sTable)

Name    Email    List
User1   [e-mail]  No
User2   [e-mail]  Yes
User3   [e-mail]  No
User4   [e-mail]  Yes
User5   [e-mail]  Yes
User6   [e-mail]  No
User7   [e-mail]  Yes

The Destination table: (dTable)

SomeStuff    UserB        Otherstuff
Stufflkas    {dropDown}   MoreData
ect.....

Desired drop down list:

User2
User4
User5
User7

I am making an excel table that is going to have a data validation drop down (UserB column in dTable) to select data from another table (sTable) on another sheet in the same workbook. the only problem that i am having is that I would like the list of users to be filtered so that only users with "Yes" in the List column will show up on the drop down for the UserB column in the dTable.

I tried with an indirect, but i couldn't get the data to filter. Is there a way to filter this in the data validation formula? or is that even possible?

回答1:

Likely the best you could do is use an array formula in a separate location to pull out and consolidate the "Yes" users, and point your drop-down to that range.

If you want to exclude the blanks from the drop-down then create a defined name with a dynamic range: eg.

=OFFSET(Sheet1!$E$2,1,0,COUNTA(Sheet1!$E$3:$E$7)-COUNTBLANK(Sheet1!$E$3:$E$7),1)

Then use that name as the source for your validation list.