Data Validation Query - Size of list alters

[Dazzybeeguy]

Hi - Is there a way to set a Data validation list / drop down that changes if more or less rows are populated.

To Explain;

If I had a list of names in A2 - A12 but in the future there may be fewer or more names can I set up up my link to that list / range so it expands / contracts to fit, I am hoping to have the drop down only show the populated names in the drop down.

I hope that makes sense

 

[kvsrinivasamurthy]

Use this formula for validation.
Select range for validation.
Data--->Data validation--->Allow - select List ---> Source Enter the formula.

=INDIRECT("A2:A"&1+COUNTIF($A$2:$A$200,"*?"))