Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 202
Default 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)
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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)


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default 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)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default 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)



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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)




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default 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)




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 202
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default 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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 202
Default Data Validation Q

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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation Data using Validation Table cell range..... Dermot Excel Discussion (Misc queries) 16 January 5th 10 10:35 PM
Data Validation Update Validation Selection PCreighton Excel Worksheet Functions 3 September 11th 07 03:32 PM
data validation invalid in dynamic validation list ilia Excel Discussion (Misc queries) 0 November 7th 06 01:54 PM
data validation invalid in dynamic validation list ilia Excel Worksheet Functions 0 November 7th 06 01:54 PM
Data validation with validation lists and combo boxs Keith Excel Discussion (Misc queries) 1 October 12th 06 11:08 AM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"