Blank Cell Validation

N

Nado

If I have an array of data in cells A1:A10 where some are blank, say th
data is a,b,c,d,e,f with the rest of the cells blank. Say I place
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
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
validation on but ignore the blanks. Any help would be appreciated
Thank
 
D

Debra Dalgleish

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
 
Top