unique values in a cell base on another cell
 

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

unique values in a cell base on another cell
 

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)


"Jay" wrote in message
...
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

unique values in a cell base on another cell
 

Select C1:C20, do datavalidationcustom 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!"

--

Regards,

Peo Sjoblom

"Jay" wrote in message
...
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