List sorting

[nathan_savidge]

Hi

I have data validation on a cell, that is taking its options from another
range of cells. This works fine, however the list is defaulting to the blank
entries. I wish it to default to the 1st option in the list.

Thanks

Nathan.

 

[Gary''s Student]

If your data validation list contains blanks, use another list that does not.

Say the list is now from A1 thru A500. In G1 thru G500 enter the array
formula:

=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$500,SMALL(IF($A$1:$A$500<>"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

and copy down. (array formulas are entered with CNTRL-SHFT-ENTER rather
than just touching the ENTER key)