Data Validation?

[Carly]

I have 4 cells (A1, A2, A3 & A4) and each cell can only have the values 1, 2, 3 or 4 in them. I know I can do this with Data Validation, but I would like to be able to restrict the user from entering the same number in 2 cells.

The user would be expected to enter one of the values 1 to 4 into the 4 cells, but not the same number in more than one cell.

For Example

1:2:3:4
4:3:2:1
1:4:3:2

Not

1:1:3:4

Hope this makes sense.

Is there any way this can be done?

 

[doofusboy]

Highlight your input cells, select Data Validation from the Excel menu, select Allow: Custom and then enter this formula:

=AND($A1<5,COUNTIF($A:$A,$A1)=1)


* just realized you want validation in same row, not down a column; will try to rework formula when I get a minute

 

[doofusboy]

Here ya go (this works for cells A1:D1):

=AND(A$1<5,COUNTIF(1:1,A$1)=1)

 

[Carly]

Thanks for replying, I changed it slightly to this

=AND(A1<5,COUNTIF($A1:$D1,A1)=1)

Is there anyway you can have 2 lots of data validation (i.e. A list of 1 to 4 & then this forumla?)

 

[doofusboy]

Is there anyway you can have 2 lots of data validation (i.e. A list of 1 to 4 & then this forumla?)

Not sure I understand what you mean. Doesn't the "A1<5" essentially give you a list of 1 to 4? [I guess technically you could enter 0 or negative numbers, but you could stop that by adding "A1>0"]

 

[Carly]

It does stop you from entering anything other than 1 to 4 but doesn't drop down a list.
Was just trying to save and explanation to the end user.