unique values in a cell base on another cell

[Guest]

I have a column with 20 cells (a1:a20). Those cells can only be filled with
5 different text values. In another column (c1:c20) I want to force a unique
value being entered base on the text in a1:a20. For example:

Bob 1
Sally 2
Ted 1
Ted 3
Sally 1
Joe 1

Invalid would be Sally assigning a value of 1 to two of her entries:

Bob 1
Sally 1
Ted 1
Ted 3
Sally 1
Joe 1

 

[Bob Phillips]

Use Data validation on C1:c20 with a formula of

=NOT(SUMPRODUCT(--($A$1:$A$20=A1),--($C$1:$C$20=C1))>1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

Select C1:C20, do data>validation>custom and

=SUMPRODUCT(--($A$1:$A$20=A1),--($C$1:$C$20=C1))<=1

on the error alert tab put in a message saying something like

"Only unique values can be assigned, you have already entered this value!"