Data Validation Formula Q
 

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)

Data Validation Formula Q
 

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" wrote in message
...
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)

Data Validation Formula Q
 

Max works like a dream, thanks

Data Validation Formula Q
 

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

Data Validation Formula Q
 

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" wrote in message
...
Max works like a dream, thanks

Data Validation Formula Q
 

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

Data Validation Formula Q
 

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

Data Validation Formula Q
 

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
---
"Seanie" wrote in message
...
Max, I think your 2nd post will only allow 1 "CC" to be entered,
whereas I want to allow multiple.

Data Validation Formula Q
 

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
---

Data Validation Formula Q
 

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",J 10="CC4"),COUNTIF
($J10:J22,J10)=1)

Data Validation Formula Q
 

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" wrote in message
...
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",J 10="CC4"),COUNTIF
($J10:J22,J10)=1)

Data Validation Formula Q
 

On Jan 9, 1:41*am, "Max" wrote:
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
---"Seanie" wrote in message

...



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",J 10="CC4"),COUNTIF
($J10:J22,J10)=1)- Hide quoted text -

- 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)

Data Validation Formula Q
 

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" wrote
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)

Data Validation Formula Q
 

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

Data Validation Formula Q
 

... 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
---
"Seanie" wrote in message
...
Max, thats exactly what I want, although I can't get my head around
how it does it