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.forum.ozgrid.com/index.php?attachment/72483/
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]
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.
Don’t have an account yet? Register yourself now and be a part of our community!
