Validation

[Panew]

I want to base my validation on a list, but sometimes the
list has blank cells in it (deliberately). So when the
validation drop-down box is used gaps appear. Is there any
whay to get Excel to skip the blank cells, and only list
cells within the list that aren't empty.

Many thanks.

 

[Frank Kabel]

Hi
you could use a helper column which skips the blank
entries and use this helper column for your data
validation as list source. e.g. If column A contains the
list with your entries (including blank cells) enter the
folowing formula as array formula (with CTRL+SHIFT+ENTER)
in B1
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW
($A$1:$A$100))))
and copy this down