Ignore Blank

[Khalil Handal]

Hi to all,
I have a range name that might have blank cells. I used it in a "validating
data" settings.
I hade a sourse=rangeName and also have chose the "list" option.
The "Ignore Blank" is selected.
When I use this list I can see the Blank cells!!
Why this option is not working ????

 

[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. To turn prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.


Gord Dibben MS Excel MVP

 

[JE McGimpsey]

"Ignore Blank" refers to the cell to which you apply validation, not the
list used in the dropdown (e.g., so that the user can leave the cell
blank).

A side effect when the list contains a blank is that any manual entry is
allowed in the cell. You should redefine your list to contain only valid
values.

This is well documented in Help (for instance, the 'Types of data
validation settings' topic).

 

[Khalil Handal]

Thanks to both of you for making it clear.
Khalil

 

[AdamV]

To get rid of the blanks so they do not appear in the list you can use an
Offset and Count (or CountA as appropriate if these are alpha rather than
pure numeric) to include only the cells with something in.

So, if your potential range of items for the list is in A1:A20 you could use:

=OFFSET('SomeSheet'!$A$1,0,0,COUNT('SomeSheet'!$A$1:$A$20),1)

I would usually implement this by using the formula as a defined name, then
simply referencing the name in the Validation dialogue.

If using COUNTA don't forget this counts cells with Anything in - even
formulae that evaluate to "", so you might need to modify to use a
COUNTA-COUNTBLANK if that is the case