Duplicate Alerting

Dai Jones · Nov 29th 2004

I have a problem that is driving me nuts here.

I have a range of cells C5:C12. Users can enter any number from 1 to 10 in these cells. However, they are not allowed to have more than one cell with the value 5 in it. Other duplicates are OK.

What I am after is a formula I can put in C15 to display a message saying "OK" or "Duplicate Fives" if they break the rule.

As always, many thanks in advance for any help offered.

Dai

Barrie Davidson · Nov 29th 2004

Re: Duplicate Alerting

Code

=IF(COUNTIF(C5:C12,5)>1,"Duplicate Fives","OK")

Regards,

Dai Jones · Nov 29th 2004

Re: Duplicate Alerting

Thanks Barrie, it worked a dream.

Glad to see there are other Canucks working late! (in BC myself)

Thanks again.

Dai

Dave Hawley · Nov 29th 2004

Re: Duplicate Alerting

Hi Dai

As pevention is better than cure I would use Data>Validation in addition to the formula.

Select C5:C12 starting from C5

Go to Data>Validation and choose Custom from the Allow box:

Add this Formula: =COUNTIF($C$5:$C$12,C5)<2 and then set an Input and Error alert (optional).

Duplicate Alerting

Participate now!

Share