Data Validation

[JDobbsy1987]

Hi,

Can someone please help with some custom Data Validation.

I need to enter a formula that only allows the following?

• Only allow entries of 6 digits in length
• Do not allow duplicates in that column

Can anyone help with this?

Regards

[Sadath31]

Assume you want the validation in Column A
then select range A2: to down and apply the custom validation

=AND(LEN(A2)=6,ISNA(VLOOKUP(A2,$A$1:A1,1,0)))

[Haseeb Avarakkan]

Hello,

Assume decimals also not allowed. try this;

=AND(INT(A1+0)=A1+0,LEN(A1)=6,COUNTIF(A$1:A1,A1)<=1)

[JDobbsy1987]

Hi,

I have tried both of these and it still allows me to insert less/more than 6 digits and allows duplicates?

Does it make a difference that my numbers are stored as text? - sorry for not mentioning if it does (i have them stored as text due to the number 0 at the beginning, without it stored as text it removes the 0's)

Regards,

[Sadath31]

It should work, see attached workbook

[JDobbsy1987]

Sorry my bad

It does work, i forgot to change the cell reference.

Thank you so much for your help