Create a data validation list using only specific data from a table

  • Please see attached workbook.


    I would like to create a dropdown list that only contains only the names in the first column that correlate to a "P" in the second column of the table.


    The table will have rows added to it regularly and the dropdown list should update automatically.


    I feel like this may be very easy, but I simply cannot figure out the data validation.ozgrid.com/forum/core/index.php?attachment/72483/

    Files

    • Example.xlsx

      (9.21 kB, downloaded 148 times, last: )
  • Re: Create a data validation list using only specific data from a table


    As long as the second column is sorted so that the "P" are grouped together, you can use a formula in your data Validation >List:


    For your sample file:


    [COLOR="#0000FF"]=OFFSET($B$5:$B$21,MATCH("P",$C$5:$C$21,0)-1,,COUNTIF($C$5:$C$21,"P"),1)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Create a data validation list using only specific data from a table


    That works great, thank you.


    However, the "P" will not always be grouped together since the list will ultimately be sorted alphabetically by name.


    Is it possible to modify the code to work in this instance?

  • Re: Create a data validation list using only specific data from a table


    Hi,


    In order to do that we will need to extract the "P" matches to another column to keep them grouped.


    First we will add a helper column that numbers the "P" entries.


    We'll keep the formulas in the existing table so that the formulas automatically become dynamic with the original data...


    in D5 add formula:


    [COLOR="#0000FF"]=IF(C5="P",COUNTIF(C$5:C5,C5),"")
    [/COLOR]
    This should copy down automatically.


    Then in E5 enter formula:


    [COLOR="#0000FF"]=IFERROR(INDEX($B$5:$B$21,MATCH(ROWS($F$5:$F5),$D$5:$D$21,0)),"")[/COLOR]


    This should extract only the names with "P" in column B and group them together.


    Now for the validation formula in G4 use:


    [COLOR="#0000FF"]=OFFSET($E$5:$E$21,,,COUNTIF($C$5:$C$21,"P"),1)[/COLOR]


    or


    [COLOR="#0000FF"]=OFFSET($E$5:$E$21,,,MAX($D$5:$D$21),1)[/COLOR]


    you can hide columns D:E if you desire.

    Files

    Where there is a will there are many ways. Finding one that works for you is the challenge!