Data Validation Q
 

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)

Data Validation Q
 

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

"Seanie" wrote in message
...
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)

Data Validation Q
 

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:

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)

Data Validation Q
 

=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

"Seanie" wrote in message
...
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)

Data Validation Q
 

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

"Ashish Mathur" wrote in message
...
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

"Seanie" wrote in message
...
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)

Data Validation Q
 

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

"Seanie" wrote in message
...
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)

Data Validation Q
 

On Jun 17, 9:20*am, "Bob Phillips" wrote:
=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

"Seanie" wrote in message

...



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)- Hide quoted text -

- 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

Data Validation Q
 

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

"Seanie" wrote in message
...
On Jun 17, 9:20 am, "Bob Phillips" wrote:
=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

"Seanie" wrote in message

...



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)- Hide quoted text -

- 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

Data Validation Q
 

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

Data Validation Q
 

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.

--
__________________________________
HTH

Bob

"Seanie" wrote in message
...
Thanks Bob that seems to work exactly the way I want it to