preventing duplication

[lezawang]

Hi
I know the formula to use to prevent duplication, thanks for your help. So I went to Data Validation->custome then wrote this formula

= countif($a1:$a5,a1)=1

but did not fully understand it

here is my understanding

This formula will be apply on all the range

=countif(range,a1) =1
=countif(range,a2) =1
=countif(range,a3) =1
=countif(range,a4) =1
=countif(range,a5) =1

let say i want to enter 100 in cell a4, so the formula is =countif(range,a4)=1, so this formula is true because I have a1= 100 therefore excel wont accept me to enter that value. Am I right?

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]100[/TD]
[/TR]
[TR]
[TD]200[/TD]
[/TR]
[TR]
[TD]300[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

You have the main idea. However, that formula only works on A1. CF adjusts the formula for each cell, just like copying and pasting a formula works on the sheet. So if you select A1:A5, and enter a DV rule equal to:

=countif($a1:$a5,a1)=1

then in A1 it's: =countif($a1:$a5,a1)=1
then in A2 it's: =countif($a2:$a6,a2)=1
then in A3 it's: =countif($a3:$a7,a3)=1
etc.

So you need to enter a different formula in each cell, or better, change the formula to:

=countif($a$1:$a$5,a1)=1

You could use a named range too.