Validation from Exclusion list

[Jim]

Hello all,

Is there any way in Excel if I want to do this:
I have a list of code that MUST NOT be entered by user(let's called this
'blacklist")
Objective is when user enter a code that is in the 'blacklist' then he got a
Warning otherwise, the code is OK. eg. the black list is 1,2,3,4,5. and when
he enter 3, the validation will look at the list and it will give a warning.
but when he enter 0 or other than in the list, then the code can be entered
Thanks

 

[Roger Govier]

Hi Jim

Select the range of cells you want to apply the validation (I am assuming
they are in column A)
Data>Validation>Formula> =OR(A1<1,A1>5)
On the Error tab, type a message like "Cannot use values between 1 and 5"

Change A1 to the first cell reference in the range you have selected.

 

[Jim]

Thanks for your prompt response Roger
It was just an example while the fact is the list is:
Unsorted and contained char eg. A231,F432, or B332, etc. This list will be
updated/added based on input from other department. What I want is that user
may not enter codes that are in the list.

Thanks b4

 

[Roger Govier]

Hi Jim

In which case, create a list of your "bad" values.
Say this is in column A of Sheet2.
Insert>Name>Define>Name> List1

Refers to> =Sheet2!$A$1:INDEX(Sheet2!$A:$A),COUNTA(Sheet2!$A:$A))

This list is dynamic and will grow as you add more entries to column A on
Sheet2

Now, in your Data Validation cells use Custom> =ISNA(MATCH(A1,List1,0))

 

[Jim]

Thanks a lot Roger. You make my life brighter!
Cheers