Data Validation

[GLHEC-BLS]

Hello,

I am trying to validate user entry in a cell that prohibits them from
entering the same number as any number in a range of cells. For example, I
want the entry in A1 to not equal A2,A3, or A4. I also do not want A2 to
equal A1,A3, or A4 and so on. When ion the data validation window I select
"not equal to" and then it won't let me enter a range of cells no matter how
many different ways I try. There has to be a way of doing this. Suggestions?
Thanks a lot.

 

[Gary''s Student]

First clear A1 thru A4

Then Click on A1 and:
Data > Validation > Settings > Custom > Formula >

=COUNTIF($A$1:$A$4,A1)<2

Finally copy A1 to A2 thru A4

 

[GLHEC-BLS]

That's what I was looking for! Thanks for the quick response

 

[GLHEC-BLS]

Actually I could not get that to work if the cells are empty before the user
inputs their numbers. Then if they try to go back and change a number to
another that is within the cell range, then they get the error. From first
time going through from top to bottom (A1-A4) they are still able to enter
the same number in each cell. Any other suggestions?

 

[GLHEC-BLS]

Ahh, nevermind. I copy and pasted the cells which changed the range in the
data vallidation formula. I changed the range in the COUNTIF function to
remain constant for each cell with only the "criteria" segment of the COUNTIF
funtion changing to reflect the cell I was setting the data validation for.
Thanks!