Data Validation Q

  • Thread starter Thread starter Seanie
  • Start date Start date
S

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)
 
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
 
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:
 
=AND(OR(AND(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),
COUNTIF($J10:$J22,J10)=1,MOD(J10,111111)<>0)
 
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
 
Seanie, Bob's MOD(J10,111111)<>0 is a smart invention, use it!
Stefi

„Bob Phillips†ezt írta:
 
=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 -
Click to expand...

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
 
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 -
Click to expand...

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
 
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.
 
Back
Top