| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation Q
Thanks Bob that seems to work exactly the way I want it to
|
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
| Data Validation Update Validation Selection | Excel Worksheet Functions | |||
| data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
| data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
| Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Hybrid Mode
