blanks in data validation list dropdown

[confused]

Hi,

I am uing data validation on cell A1 and choosing to allow from a list.
In the source box, I am clicking on column L (source =$L:$L). I have
selected the 'ignore blank' checkbox.

column L has 13 names. If there is something in columns A, B, C....etc in
rows 14 or greater then the dropdown list in cell A1 shows blanks along with
the names from column L.

does anyone know how I stop this happening?

Thanks for any help you cangive

 

[Toppers]

If the source is fixed at 13 rows then use $L$1:$L$13 for the source.

 

[Mark Lincoln]

This *is* interesting. This behavior also occurs if you have data in
the same row as a blank cell in the validation area when you specify a
finite range rather than a whole column. But it does not occur when
you have data a different column from that in which the validation is
defined.

What to do? You could move your validation data down below your other
data and set your print area to not include the validation area(s).
For that matter, you could move it to column A, below your working
data. Or perhaps somebody else on this list has a better answer,
because for now I don't.

Too bad validation data can't be on a separate sheet.

 

[Mark Lincoln]

Oops! It turns out that data in any column that is in the same row as
a blank cell in the validation table causes the problem. Yuck.

 

[Mark Lincoln]

I found a solution. Define a named range on another sheet. Then in
the Data Validation dialog, enter as the data validation source:

=namedrange

changing "namedrange" to the name of your data validation range.

Note that data next to blank cells in the named range will still cause
the blank lines to appear. To prevent this, use that sheet only for
data validation tables and put the tables in a single column so that
there is no data next to them. (This may be why I've never seen this
behavior before. All my validation tables in the few spreadsheets in
which I use them happen to be in single columns.)

 

[Dave Peterson]

If you're trying to make it easy to add more names to that list, then you could
use a dynamic name--one that expands and contracts with the amount of data in
that column.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

 

[confused]

this answered the next question I had, thanks Dave!

 

[confused]

this works a treat, thanks Mark!

I found a solution. Define a named range on another sheet. Then in
the Data Validation dialog, enter as the data validation source:

=namedrange

changing "namedrange" to the name of your data validation range.

Note that data next to blank cells in the named range will still cause
the blank lines to appear. To prevent this, use that sheet only for
data validation tables and put the tables in a single column so that
there is no data next to them. (This may be why I've never seen this
behavior before. All my validation tables in the few spreadsheets in
which I use them happen to be in single columns.)