Validation from Exclusion list
 

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

Validation from Exclusion list
 

Hi Jim

Select the range of cells you want to apply the validation (I am assuming
they are in column A)
DataValidationFormula =OR(A1<1,A15)
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.
--
Regards
Roger Govier

"Jim" wrote in message
...
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

Validation from Exclusion list
 

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" wrote:

Hi Jim

Select the range of cells you want to apply the validation (I am assuming
they are in column A)
DataValidationFormula =OR(A1<1,A15)
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.
--
Regards
Roger Govier

"Jim" wrote in message
...
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

Validation from Exclusion list
 

Hi Jim

In which case, create a list of your "bad" values.
Say this is in column A of Sheet2.
InsertNameDefineName 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))
--
Regards
Roger Govier

"Jim" wrote in message
...
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" wrote:

Hi Jim

Select the range of cells you want to apply the validation (I am assuming
they are in column A)
DataValidationFormula =OR(A1<1,A15)
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.
--
Regards
Roger Govier

"Jim" wrote in message
...
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

Validation from Exclusion list
 

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

"Roger Govier" wrote:

Hi Jim

In which case, create a list of your "bad" values.
Say this is in column A of Sheet2.
InsertNameDefineName 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))
--
Regards
Roger Govier

"Jim" wrote in message
...
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" wrote:

Hi Jim

Select the range of cells you want to apply the validation (I am assuming
they are in column A)
DataValidationFormula =OR(A1<1,A15)
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.
--
Regards
Roger Govier

"Jim" wrote in message
...
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