Limiting Value in a range of cells in Excel 1

[starbird]

Hello there. I am running Excel XP, and want to know if I can format a cell range (say a column) to not allow duplicate numbers.

Is this possible?

Thanks.

 

[KenWright]

Data / Validation / Custom / Formula:-

=COUNTIF($A$10:$A$27,A10)<2

Select cell A10, copy it and paste it across A11:A27. Now try entering two duplicates.

Note - Data Validation can be bypassed by a simple copy and paste from another cell though.

Regards
Ken..............

 

[starbird]

Thank you. Works perfectly. Thanks again.

 

[KenWright]

My pleasure - just for info, you can also use a similar function in Conditional formatting to analayse existing sets of data and highlight any existing duplicates.

=COUNTIF($A$10:$A$27,A10)>1

Regards
Ken.............