Problem

G

Guest

I also need to make sure that '10', '20', '30', '-5', '-10', '-15' only
appears once in the cells B12:16, B26:31. Any combonation of numbers (i.e.
'10' in B14 and '-15' in B26) also needs to be highlighted. Thanks again for
your help!
 
S

Sandy Mann

Well, it works for me. I cannot think of anything that would cause what you
are getting if all your cells are referencing the same list.

I'm just off to bed now, if you want, you can send me a copy of your sheet
and I will take a look tomorrow.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
S

Sandy Mann

Just to complete the thread for the archives, the OP wanted conditional
formatting for any duplicate value in a row or any two of the values he gave
below in a row.

Two suggestions, with 2 conditional formats:

Any duplicate value:
=AND(B12<>"",COUNTIF($B12:$U12,B12)>1)

Any two of the values below:
=AND(B12<>"",SUMPRODUCT(--(B12=$AA$1:$AA$7))=1,SUMPRODUCT(--($B12:$U12=$AA$1:$AA$7))>1)

Or both conditions in one formula:
=AND(B12<>"",OR(COUNTIF($B12:$U12,B12)>1,AND(SUMPRODUCT(--(B12=$AA$1:$AA$6))=1,SUMPRODUCT(--($B12:$U12=$AA$1:$AA$6))>1)))

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Top