Test for Values Q
 

I have 13 input boxes in J10:J22. Within Data Validation I test to
ensure that inputs into these cells are between 1-9999.

How can I test that no input in J10:J22 is duplicated? I assume I have
to do this via code, how would I do that?

Test for Values Q
 

Select cells J10:J22
Goto DV
Change the Allow type to Custom
Add this formula
=AND(J10=1,J10<1000,COUNTIF($J$10:J10,J10)=1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
ups.com...
I have 13 input boxes in J10:J22. Within Data Validation I test to
ensure that inputs into these cells are between 1-9999.

How can I test that no input in J10:J22 is duplicated? I assume I have
to do this via code, how would I do that?

Test for Values Q
 

Bob, where does it reference cells J11:J22 within this formula?

Test for Values Q
 

Sean,

It is automatically adjusted by Excel because you start by selecting
J10:J22.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
ups.com...
Bob, where does it reference cells J11:J22 within this formula?

Test for Values Q
 

Bob

Very clever, thanks. One other tweak, how could I allow a max of only
6 digits, within the same restrictions as above. So user could enter 1
or 123456 provided these weren't also entered first in J10:J22?

Test for Values Q
 

Bob

Very clever, thanks. One other tweak, how could I allow a max of only
6 digits, within the same restrictions as above. So user could enter 1
or 123456 provided these weren't also entered first in J10:J22?

Test for Values Q
 

Sorry, I am not quite clear on that one.

Is the 1 or 123456 in J10:J22 or in some other cell that checks that that
number hasn't already been used in J10:J22? If the latter, use a formula in
this new cell (let's say L10) of

=AND(L10=1,L10<=999999,NOT(ISNUMBER(MATCH(L10,$J$ 10:$J$22,0))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
ups.com...
Bob

Very clever, thanks. One other tweak, how could I allow a max of only
6 digits, within the same restrictions as above. So user could enter 1
or 123456 provided these weren't also entered first in J10:J22?

Test for Values Q
 

Sorry Bob, I should have said values should be no longer than 6 digits
and not between 1-99999

The rules should be:

1) Values entered in J10:J22 must be no longer than 6 digits
2) No 2 values entered in J10:J22 should be the same

I twigged your formula above =AND(J10=1,J10<1000,COUNTIF($J
$10:J22,J10)=1) to solve 2) above, but not sure how to account for 1)
above

I've tried =AND(LEN(L20=1),LEN(L20<=999999),NOT(ISNUMBER(MAT CH(L20,$J
$10:$J$22,0)))) - this is input in J20

But its not right

Test for Values Q
 

I am really faltering now.

Is the formula in J10:J22 okay, or are you trying to modify that?

Is this a formula for a different range?


If it is the former, that is what the formula does, with the COUNTIF.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
ps.com...
Sorry Bob, I should have said values should be no longer than 6 digits
and not between 1-99999

The rules should be:

1) Values entered in J10:J22 must be no longer than 6 digits
2) No 2 values entered in J10:J22 should be the same

I twigged your formula above =AND(J10=1,J10<1000,COUNTIF($J
$10:J22,J10)=1) to solve 2) above, but not sure how to account for 1)
above

I've tried =AND(LEN(L20=1),LEN(L20<=999999),NOT(ISNUMBER(MAT CH(L20,$J
$10:$J$22,0)))) - this is input in J20

But its not right

Test for Values Q
 

Sorry about this Bob.

The following formula, placed in J10:J22 works great, in that it will
not allow duplicates be entered in the range J10:J22

=AND(J10=1,J10<9999,COUNTIF(J$10:J$22,J10)=1)

What I'm trying to do now is, instead of the criteria
(J10=1,J10<9999), to have a criteria that the values entered in
J10:J22 must be between 1 and 6 characters in length.

The resultant DV will then check for duplicates and ensure that the
values entered are between 1 & 6 characters long

Test for Values Q
 

Okay Sean, we'll get there.

Just check to test for values between 1 and 999999

J10=1,J10<=999999

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
ups.com...
Sorry about this Bob.

The following formula, placed in J10:J22 works great, in that it will
not allow duplicates be entered in the range J10:J22

=AND(J10=1,J10<9999,COUNTIF(J$10:J$22,J10)=1)

What I'm trying to do now is, instead of the criteria
(J10=1,J10<9999), to have a criteria that the values entered in
J10:J22 must be between 1 and 6 characters in length.

The resultant DV will then check for duplicates and ensure that the
values entered are between 1 & 6 characters long

Test for Values Q
 

Bob, thanks I just couldn't see it. I had in my head that
(J10=1,J10<99999) was values entered up to 99999 and of course any 6
digit value can go up to 99999, doh!

Thanks again