data validation help

[viper]

I'm having trouble with my validation formula. I'm wanting to check for duplicate invoices and I am using this formula:
=Countif($C$9,C9)=0
I have a range of cells (C9:C38) that I copied this formula to. My problem is that the validation only gives the error message if the new invoice is the same as in C9, I can put the same invoice number in cells C10:C38 and no error message. What's wrong?

Thanks,

[Aladin Akyurek]

On 2002-04-01 04:31, viper wrote:
I'm having trouble with my validation formula. I'm wanting to check for duplicate invoices and I am using this formula:
=Countif($C$9,C9)=0
I have a range of cells (C9:C38) that I copied this formula to. My problem is that the validation only gives the error message if the new invoice is the same as in C9, I can put the same invoice number in cells C10:C38 and no error message. What's wrong?

Thanks,

What do you have in C8?

[Dave Hawley]

Hi viper

Try changing the Range argument of the COUNTIF to:


$C$9:$C$38

[viper]

Aladin in cell C8 I have the heading, and Dave I changed the formula to $C$9:$C$38 but now I get the error message in each cell. I've done this before I just can't remember how or the formula I used.

[Russell Hauf]

You want the COUNTIF to equal 1, not 0.

Hope this helps!

-Russell

[viper]

Thanks to all,
I worked it out and it seems to be working as I need.

Thanks,

[Aladin Akyurek]

On 2002-04-01 17:24, viper wrote:
Aladin in cell C8 I have the heading, and Dave I changed the formula to $C$9:$C$38 but now I get the error message in each cell. I've done this before I just can't remember how or the formula I used.

=COUNTIF($C$8:C8,C9)=0

to be used in data validation that you set up in C9, which you copy/propagate down to relevant cells.

Aladin