Data/Validation

A

AndyR

Hi

I am using Data/Validation to provide a drop down menu listing th
values from a range of cells. My problem is that the information i
the range changes frequently and there are always blank cell
throughout the list. Because of this, when you click on the drop dow
menu there are large gaps between the choices. Any idea how I set i
to ignore blank cells within the range?

Any help would be appreciated

Thanks
And
 
F

Frank Kabel

Hi
you could use a helper column for your list data. e.g. your data is in
A1:A100, then enter the following array formula (with CTRL+SHIFT+ENTER)
in B1:
=IF(ISERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)
),ROW(1:1)))),"",INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A
$100)),ROW(1:1))))
and copy this formula down to B100
Now use this column as source for your validation
 
Top