Exclusion List for Data Validation??

  • Thread starter Thread starter Lostguy
  • Start date Start date
L

Lostguy

Hello!

I have cells that require an entry (last names of people), so I have
the validation set up for text >1 and <1000.

This is good if the user decides to leave it blank, but if they put in
a space, or two spaces, or more, or put in words like "none" "N/A"
"unknown", etc, those responses are unacceptable. So how can I set up
validation to allow for any entry except for blanks, spaces, or a list
of words that I will constantly have to adjust as users get more
creative?

Thanks!

VR/

Lost
 
It would be really difficult to try and trap *every possible* illegal entry
since the possibilities are basically unlimited.

Try something like this:

In a range of cells create 2 lists of the illegal entries. One list will be
for single characters like numbers, spaces and punctuation marks. The other
list will be for whole words like none, unknown, N/A etc.

The reason for 2 lists is because you can easily search for single
characters like numbers and punctuation marks because they most likely are
not part of a legitimate name except in "rare" cases (O'Neil). Trapping
whole words is more complicated because the word might be part of a
legitimate name.

H1 = <space> (a literal space character)
H2 = 0
H3 = 1
H4 = 2
List all the individual digits up to 9
H11 = 9

I1 = none
I2 = unknown
I3 = n/a

Create dynamic defined names for these listed items. The first list could be
named Numbers and the second list could be named Words. Making the lists
dynamic allows for expansion without having to edit the validation formula
every time you add a new illegal entry to either list.

The dynamic range formulas would be:

Name: Numbers
Refers to:

=$H$1:INDEX($H$1:$H$100,COUNTA($H$1:$H$100))

Name: Words
Refers to:

=$I$1:INDEX($I$1:$I$100,COUNTA($I$1:$I$100))

Then you validation formula would be:

=AND(COUNT(FIND(Numbers,A1))=0,ISNA(MATCH(Words,A1,0)))

This will not be "perfect" but what you want to do is practically
impossible!
 
Back
Top