Ignoring Blanks in Data Validation

[Scott]

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.

 

[T. Valko]

If you're using the range of cells as the source for a drop down list then
the source has to be a contiguous range (no empty cells between entries).

 

[Chip Pearson]

There isn't really an easy way to do this. And the "Ignore Blanks"
option in Validation indicates whether blanks are allowed to be
entered into the validated cell, not to ignore blanks in the
validation list. (And even this broken. If you clear the Ignore Blanks
setting in Validation, you cannot delete with the Back Space key a
validated cell, but you can with the Delete key. It is broken.)

If you want to remove blank elements from the validation list, try
this. Assign the name "BlanksRange" to the range of cells that contain
the valid values along with the blank cells. Then, create another
defined name of "NoBlanksRange" referring to a range of cells that
should contain the non-blank values from BlanksRange. Enter the
following array formula in the first cell of NoBlanksRange and fill
down through the entire NoBlanksRange.

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

This is an Array Formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. The formula will not
work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/Excel/ArrayFormulas.aspx for much more
information about array formulas in general.

This formula will place all of the values in BlanksRange at the top of
NoBlanksRange, and the rest of NoBlanksRange will be empty.

Next, you need to create a formula that will create the address of the
top part of NoBlanksRange, referring only to those cells in
NoBlanksRange that have values. Use the following formula to do this:

=ADDRESS(ROW(NoBlanksRange),COLUMN(NoBlanksRange))
&":"&ADDRESS(MAX(ROW(NoBlanksRange)*(NoBlanksRange<>"")),COLUMN())

This, too, is an Array Formula, so be sure to press CTRL SHIFT ENTER
rather than just ENTER. Name the cell that contains this formula
"ValAddr".

Now, select the cell to which you want to add validation, open the
Data Validation dialog, choose List in the Allow list, and enter

=INDIRECT(ValAddr)

in the Source box. The INDIRECT function tells Validation to look in
the cell named ValAddr, get the value of that cell (which is a range
address), and then use that range.

So, in the end, you should have something like....


Name=BlanksRange
first
second
<blank cell>
third
<blank cell>
last

Name=NoBlanksRange
first
second
third
last
<blank cells follow>

Name = ValAddr
$H$11:$H$20 ' or whatever come from NoBlanksRange.


Cell To Validation
Validation = =INDIRECT(ValAddr)


Sounds like a lot of work? Yeah, it is, but I'm not aware of any other
way.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

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