Use a set of values to validate cell input

[Aguspwc]

Hi!

I searched a solution for this and didn't find any.
What I want to do is to validate that the cell value in "Input" is included in the "Validation list" column. For example:

I can't use a simple data validation with a list because the "List" column is going to be filled using copy & paste, so the validation would be erased.
Is there a way to get this just with formulas? I can't use macros either.

I tried this formula but doesn't work in the way I need.

=IF(B2<>D2:D8;"Error";"OK")

I'd appreciate any help on this. Thanks in advance!

 

[JoeMo]

Book1.xlsm
	A	B	C
1	Result	Input	List
2	OK	A	A
3	OK	F	B
4	Error	Q	C
5	OK	C	D
6	OK	A	E
7	OK	D	F
8	Error	2	G
Sheet2

Cell Formulas
Range	Formula
A2:A8	A2	=IF(SUMPRODUCT(--($C$2:$C$8=B2))>0,"OK","Error")

 

[kweaver]

I thought of 2 ways without trying anything else.

Book1
	A	B	C	D	E
1	Check	Input		Valid	Check
2	OK	A		A	OK
3	OK	F		B	OK
4	Error	Q		C	Error
5	OK	C		D	OK
6	OK	A		E	OK
7	OK	D		F	OK
8	Error	2		G	Error
Sheet3

Cell Formulas
Range	Formula
A2:A8	A2	=IF(SUM(--(B2=$D$2:$D$8))>=1,"OK","Error")
E2:E8	E2	=IF(0=SUMPRODUCT((B2=$D$2:$D$8)*(ROW($2:$8))),"Error","OK")

 

[Aguspwc]

Book1.xlsm
	A	B	C
1	Result	Input	List
2	OK	A	A
3	OK	F	B
4	Error	Q	C
5	OK	C	D
6	OK	A	E
7	OK	D	F
8	Error	2	G
Sheet2

Cell Formulas
Range	Formula
A2:A8	A2	=IF(SUMPRODUCT(--($C$2:$C$8=B2))>0,"OK","Error")

Thank you very much! I think it's just what I needed!

 

[Aguspwc]

I thought of 2 ways without trying anything else.

Book1
	A	B	C	D	E
1	Check	Input		Valid	Check
2	OK	A		A	OK
3	OK	F		B	OK
4	Error	Q		C	Error
5	OK	C		D	OK
6	OK	A		E	OK
7	OK	D		F	OK
8	Error	2		G	Error
Sheet3

Cell Formulas
Range	Formula
A2:A8	A2	=IF(SUM(--(B2=$D$2:$D$8))>=1,"OK","Error")
E2:E8	E2	=IF(0=SUMPRODUCT((B2=$D$2:$D$8)*(ROW($2:$8))),"Error","OK")

Thank you very much!

 

[JoeMo]

Thank you very much! I think it's just what I needed!

You are welcome - thanks for the reply.