+ Reply to Thread
Results 1 to 4 of 4

Filtering Data Validation List Without Using Advanced Filter

  1. #1
    Registered User
    Join Date
    06-20-2010
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Filtering Data Validation List Without Using Advanced Filter

    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!
    Last edited by idlewild; 05-15-2011 at 08:02 AM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Filtering Data Validation List Without Using Advanced Filter

    It can be done using a helper column

    A1:B10 = table

    In, say D1: =IF(B1="yes",ROW(),""), copied down to D10
    In E1: =IFERROR(INDEX($A$1:$A$10,MATCH(SMALL($D$1:$D$10,ROW()),$D$1:$D$10,0)),1)

    Copied down to E10

    Create dynamic named range referencing column-E cells
    =OFFSET(Sheet1!$E$1,0,0,COUNTIF(Sheet1!$E$1:$E$10,"<>1"),1)
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    06-20-2010
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Filtering Data Validation List Without Using Advanced Filter

    Thanks! This works very nicely.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Filtering Data Validation List Without Using Advanced Filter

    Hi,

    Would you consider changing your location please? Apart from IT techies, who I admit do often come from another planet, we can assume you are an inhabitant of Earth.

    Seriously, this helps those who respond to requests since it gives us a clue about your system locale settings - dates, currency etc.

    It particularly helps determine whether you are from the US and for some strange reason probably use the mm/dd/yy format in dates rather than the normal dd/mm/yy format that the rest of the known universe uses.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1