You misunderstand the purpose of "ignore blanks"

Blank Cells in Source List

If the source list is a named range that contains blank cells, users may be
able to type any entry, without receiving an error message.

I think what you really want is a dynamic named range that will show only
non-blank cells in the range and expand as you add more items.

See Debra Dalgleish's site for creating dynamic ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP


On Wed, 27 May 2009 13:08:03 -0700, Scott
wrote:

How do you ignore blank cells in data validation ranges? I know there's a
check box that says "Ignore Blanks" but that doesn't seem to work for me. If
I have a list of potentially 5 cells that could contain values, but only 3 of
them do, is there a way to ignore the 2 blank cells, but when there is a
value there, for it to register?

Thanks.