Test for Values Q

[Sean]

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?

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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?

 

[Sean]

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?

 

[Bob Phillips]

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]

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(MATCH(L20,$J
$10:$J$22,0)))) - this is input in J20

But its not right

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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