Blank Cell Validation
 

If I have an array of data in cells A1:A10 where some are blank, say the data is a,b,c,d,e,f with the rest of the cells blank. Say I place a validation on cell A11 using a list of A1:A10 and select ignore blanks, why is it that when I then add a letter, say h to one of the empty cell, the validation no longer ignores the blanks? It also happens when I clear one of the existing cells? Can I stop this from happening?

I want to be able to add and remove data from a list while having a validation on but ignore the blanks. Any help would be appreciated. Thanks

Selecting Ignore blanks doesn't make Excel ignore the blank cells in the
source list. The blanks will appear in the dropdown list, and users will
be able to type any value in the cell with validation.

Perhaps you could create a list without blanks in another part of the
worksheet, or on a hidden sheet, and use that as the source. There are
instructions here for creating a dynamic named range:

http://www.contextures.com/xlNames01.html

Nado wrote:
If I have an array of data in cells A1:A10 where some are blank, say the
data is a,b,c,d,e,f with the rest of the cells blank. Say I place a
validation on cell A11 using a list of A1:A10 and select ignore blanks,
why is it that when I then add a letter, say h to one of the empty cell,
the validation no longer ignores the blanks? It also happens when I
clear one of the existing cells? Can I stop this from happening?

I want to be able to add and remove data from a list while having a
validation on but ignore the blanks. Any help would be appreciated.
Thanks




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html