Validation from Exclusion list

  • Thread starter Thread starter Jim
  • Start date Start date
J

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
 
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.
 
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
 
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))
Click to expand...
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))
 
Back
Top