Data Validation Formula Q

[Seanie]

How could I tweak the Data Validation below which in cell J10 where it
resides, also include an ability to enter the letters CC?

At the moment it requires up to a 6 digit numeric value which must be
unique from the inputs in J10:J22. I still want to retain this but
also allow the letters CC. Note here I want to allow CC to be typed in
more than one cell in the range J10:J22 (but the up to 6 digit
numerics must be unique)

=AND(J10>=1,J10<999999,COUNTIF($J10:J22,J10)=1)

 

[Max]

Think you could try:
=OR(AND(J10>=1,J10<999999,COUNTIF($J10:J22,J10)=1),J10="CC")

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Seanie]

Max works like a dream, thanks

 

[Max]

Correction to earlier, try this instead, with the OR inside the AND:
=AND(OR(J10>=1,J10<999999,J10="CC"),COUNTIF($J10:J22,J10)=1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

um, ok if you tested it in order over there. For info, I've just posted a
"correction" to the earlier response after I re-read your specs (crossed
your reply here).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Seanie]

What did you expect would not happen with your first reply? It seems
ok but now I have doubts

 

[Seanie]

Max, I think your 2nd post will only allow 1 "CC" to be entered,
whereas I want to allow multiple.

 

[Max]

Yes, the 1st response is the correct one.
I misread it the 2nd time around.
Sorry for the confusion
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Max]

Dismiss the correction. It's incorrect. The 1st response's on.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Seanie]

I've tweaked by requirements a small bit and I've tried to twaek Max's
code above but not getting what I want

Instead of allowing Multiple "CC" AND up to 6 unique digit numeric
values, within the range J10:J22. I want:-

Any one of "CC1" or "CC2" or "CC3" or "CC4" (but only once used) AND
up to 6 unique digit numeric values, within the range J10:J22.

Below is my attempt but it allows me to type "CC5" and indeed any
alpha value, which I don't want to allow


=AND(OR
(J10>=1,J10<999999,J10="CC1",J10="CC2",J10="CC3",J10="CC4"),COUNTIF
($J10:J22,J10)=1)

 

[Max]

Think the COUNTIF range needs to be fixed with $ signs:
=AND(OR(J10>=1,J10<999999,J10="CC1",J10="CC2",J10="CC3",J10="CC4"),COUNTIF($J$10:$J$22,J10)=1)

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Seanie]

Think the COUNTIF range needs to be fixed with $ signs:
=AND(OR(J10>=1,J10<999999,J10="CC1",J10="CC2",J10="CC3",J10="CC4"),COUNTIF(­$J$10:$J$22,J10)=1)

--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik









- Show quoted text -

Problem with this formula is that it a) allows numeric values greater
than 6 digits and b) also any alpha value, instead of restricting the
input to either 6 numeric or CC1; CC2; CC3 or CC4 (for eg it will
accept BB)

 

[Max]

Try this revision, lightly tested ok:
=AND(OR(AND(J10>=1,J10<999999),J10="CC1",J10="CC2",J10="CC3",J10="CC4"),COUNTIF($J$10:$J$22,J10)=1)

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Seanie]

Max, thats exactly what I want, although I can't get my head around
how it does it

 

[Max]

... OR(AND(J10>=1,J10<999999),J10="CC1", ..

The nested AND above now traps it for numeric entries between the specified
range

Any TEXT will be evaluated by Excel as being greater than the largest number
in arithmetic comparisons. Hence that's why, w/o the above nested AND
earlier, for eg the text: "BB" was allowed as a valid entry.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik