+ Reply to Thread
Results 1 to 5 of 5

create a dynamic drop down list based on specific criteria

  1. #1
    Registered User
    Join Date
    02-21-2012
    Location
    Sooke, Canada
    MS-Off Ver
    Excel 2003
    Posts
    11

    create a dynamic drop down list based on specific criteria

    I have a list of employees. Each employee is classified as either "active" or "inactive"

    I want to have a dynamic drop down list box which will automatically show all "active" employees.

    I know how to create a dynamic range that will accommodate adding new employees, BUT , I need to find a way to have names removed from the list when an existing employee's status is changed to "inactive"

    It seems like it should be possible, but I have been unable to find a solution.

    Any help would be greatly appreciated.

    THX
    Last edited by stevegrobertson; 02-21-2012 at 08:10 PM.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: create a dynamic drop down list based on specific criteria

    Hi stevegrobertson,

    Welcome to the forum.
    How are you judging that the employee is active or inactive.. is it with the help of helper column or with color coding or what ? If possible, share a sample file. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    02-21-2012
    Location
    Sooke, Canada
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: create a dynamic drop down list based on specific criteria

    I have a worksheet containing data about employees. It has several columns with different data. The columns I am interested in are the "name" and "status" columns. For the purpose of clarity I have shown only these 2 columns in my example (sheet 1).
    The employee is designated as either “active” or “inactive” (the data in the “status column is validated from a list containing only those 2 values)
    I want to use the name and status data to create a dynamic list showing ONLY ACTIVE EMPLOYEES that will appear in a drop down box. I want this list to be automatically updated whenever an existing employee’s status is changed or a new employee is added.
    in sheet 2, I have devised a clumsy way of displaying a filtered list in the next column, showing only “active” employees by using the formula =IF(B2="active",A2," ") - You'll see that as I change the value from "active" to "inactive"the list in the "filtered" column is modified but this would still leave blanks in the resulting drop down list. I tried to deal with this by creating another column for the drop down list source called "no blanks" to which I applied the following array formula =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) This formula will work if the value in the adjacent “filtered” column is truly “blank”, but unfortunately, Excel “sees” these calls as having a value since they contain the formula.(if I delete the formula in a "blank" cell in the "filtered" column then the list in the "no blanks" column WILL skip that row in its' list, and the blank will not appear in the resulting drop down list in cell G4.)
    This is frustrating, since I feel I was getting close. Any suggestions?

    PS, I would eventually like the resulting list to be sorted alphabetically, but, first things, first.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: create a dynamic drop down list based on specific criteria

    Hi,

    See the attached. You can hide Sheet2, if you don't want to display it.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    02-21-2012
    Location
    Sooke, Canada
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: create a dynamic drop down list based on specific criteria

    Thanks so much!!! This seems to do exactly what I need. I should be able to set this up in my workbook by changing the name ranges and sheet references etc.
    Thanks again.

+ 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