Share via

Data Validation Based On Records From Table That Meets Criteria

Anonymous
2016-06-20T08:42:30+00:00

I am trying to create a dropdown list in a cell using data validation. The list would be based on a table (employee_tbl), however I only want specific records from the table to propagate the list. Each row in the table creates a unique record. It seems like there would be an easy solution for this, but I have searched aggressively with no luck. See the sample screenshot below for illustrative purposes:

My goal is to limit the data validation cell to [NAME]s in the table where the row is marked 'Yes' in the [ACTIVE] column. I would like for the list to be dynamic (updating as the table changes as new records are added or the [ACTIVE] cell changes. In the scenario above, Joe, Tim, Sylvia and Sue should not be in the list as they are not 'Active'.

Is this possible without manually creating a second list? Can it be done with an array? I have 100s of records and need to maintain the structure of the table. I have multiple scenarios like this that I need to create.

Please help...

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2016-06-20T10:40:12+00:00

    Hi,

    I would suggest you use a column to create a list of names marked as yes.

    Let's say we use column H for this, select cells H1 to H100, copy the following formula and press Control-Shift-Enter.

    =IFERROR(INDEX($C:$C,SMALL(IF($F:$F="Yes",ROW($F:$F),""),ROW())),"")

    Now, when you create the Data Validation list, enter the following formula into the Source:

    =INDIRECT("H1:H"&100-COUNTIF(H1:H100,""))

    Regards,

    Zach

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-23T17:22:51+00:00

    Hi Zach,

    Magic happens! I have looked all over for this solution with no luck. Thanks for the help :)

    A few followup questions on this scenario:

    1. The logic only seems to work correctly if the array is entered starting in Row 1 (H1 in this scenario). I tried beginning the array formula in H6, but it does not produce the same the results. The reason for the need to start in a lower row is only for layout aesthetics. Is there a way to do this without breaking the logic?
    2. I am fairly new to using array formulas and would like to learn more. Any explanation of your formulas would be much appreciated.

    Thanks again!

    Matt

    0 comments