List sorting

N

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.
 
G

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)
 
Top