Validation for a cell

[Guest]

Dear expert,

I want to set a cell (for example : A1) with validation so that the value in
it will not appear twice in a row, say A1:M1. My formula is:
=COUNTIF($A$1:$M$1,A1)<=1.
The above formula is working but I want the same validation also working for
Column A, say A1:A10. The PC blocks me and it states a cell cannot be set
more than one validation. Can this be done? If yes, what the formula will be?
Please advise.

Thanks in advance.

 

[Biff]

Hi!

Set the second range starting in cell A2.

Select A2:A10

Use this formula for this range:

=AND(A$1<>A$2,COUNTIF(A$1:A$10,A2)<=1)

Biff

 

[Mangesh Yadav]

Select A1:M1, and enter the validation:

=(COUNTIF($A$1:$M$1,A1)+COUNTIF($A$2:$A$10,A1))<=1

Select A2:A10 and enter:

=(COUNTIF($A$1:$M$1,A2)+COUNTIF($A$2:$A$10,A2))<=1


Mangesh