To prevent duplicate entries using Data validation.

[legendkiller420]

Hi,

I juz wanna prevent the duplicate entries in a range using the data validation. I know , we can use the "custom list" in data validation and use the formula =countif(range,criteria)=1 to prevent the duplicates. But My problem is typical..In the selected range, i will use the text "NA" and some numbers. So i juz want the numerics to be prevented in the data validation. If i use the above countif formul, it also prevents the word "NA". But i need "NA" to be allowed and to prevent only the numbers.

Can anyone help me out?

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this Custom DV formula (applied to A1:A10) do what you want?

=OR(A1="NA",COUNTIF($A$1:$A$10,A1)=1)

 

[legendkiller420]

Thanks..it works