Data Validation Q

[Seanie]

I have the Data Validation detailed below, it does a couple of things
for me, but how can I add an additional validation that will disallow
the input of the same 6 numbers eg a user that inputs 666666 or 111111
or 555555 etc?

=AND(OR(AND
(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),COUNTIF
($J10:$J22,J10)=1)

 

[Ashish Mathur]

Hi,

You can add this condition. This is an array formula (Ctrl+Shift+Enter)

=AND(EXACT(1*(MID(J10,ROW(INDIRECT("1:"&LEN(J10))),1)),1*LEFT(J10,1)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Stefi]

Insert this formula as one member of your AND function:
=NOT(AND(LEFT(A1)=MID(A1,2,1),LEFT(A1)=MID(A1,3,1),LEFT(A1)=MID(A1,4,1),LEFT(A1)=MID(A1,5,1),LEFT(A1)=MID(A1,6,1)))
Change A1 to the real reference!
Regards,
Stefi

„Seanie†ezt írta:

 

[Bob Phillips]

=AND(OR(AND(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),
COUNTIF($J10:$J22,J10)=1,MOD(J10,111111)<>0)

 

[Ashish Mathur]

Hi,

Try this shorter one - much better than my previous solution

=AND(OR(AND(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),COUNTIF($J10:$J22,J10)=1,MOD(J10,1*REPT(1,LEN(J10)))=0)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Stefi]

Seanie, Bob's MOD(J10,111111)<>0 is a smart invention, use it!
Stefi

„Bob Phillips†ezt írta:

 

[Seanie]

=AND(OR(AND(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),
COUNTIF($J10:$J22,J10)=1,MOD(J10,111111)<>0)

--
__________________________________
HTH

Bob







- Show quoted text -

I've used the formula from Bob, only thing I'm finding is that it will
not accept an input value of CC1 or CC2 or CC3 or CC4

It does, however as I wanted disallow, 6 numbers of the same being
input

 

[Bob Phillips]

This seems to work as I understand it

=(OR(IF(AND(J10>=1,J10<999999),--MOD(J10,111111)<>0),J10="CC1",J10="CC2",J10="CC3",J10="CC4"))*(COUNTIF($J10:$J22,J10)=1)

--
__________________________________
HTH

Bob

=AND(OR(AND(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),
COUNTIF($J10:$J22,J10)=1,MOD(J10,111111)<>0)

--
__________________________________
HTH

Bob







- Show quoted text -

I've used the formula from Bob, only thing I'm finding is that it will
not accept an input value of CC1 or CC2 or CC3 or CC4

It does, however as I wanted disallow, 6 numbers of the same being
input

 

[Seanie]

Thanks Bob that seems to work exactly the way I want it to

 

[Bob Phillips]

Great. I wanted to avoid the embedded IF but without it the formula always
evaluates the MOD, and errors if you input C1 etc. The IF means the MOD
doesn't get evaluated if the value is not > 1 and < 999999.