Allow only certain alphanumeric data in a range

[patni]

Hey guys,
I need a small help.

In my sheet "PRODUCT" range "A1:A150" I want it to only only certain alphanumeric data.
I want it to allow only "AK1, AK2, AK3, AK4, AK5 ....... AK100" and "OR1, OR2, OR3, OR4, OR5 ..... OR100"

If a user enters anything other than this, then I dont want excel to allow it. Probably give a error message saying "Wrong Code Entered"

Also , I already have this in my data valididation

=COUNTIF($A:$A,A11)=1

. This prevents duplicate entries

Can anyone please help me.

THANK YOU

 

[patni]

Re: How to allow only certain alphanumeric data in a range

Hey Rick Rothstein,
I just checked that.

Okay this is something intresting. Is there any ways to prevent this ?
Just very curious

 

[patni]

Re: How to allow only certain alphanumeric data in a range

Hey Special K 99,

Is it possible to add 2 more criteria in the code ? (Sorry for the trouble)
I mean we have "AK" and "OR" in the code below.

Is it possible to add "LE" and "LK" as well?

So total we have 4 criteria "AK" "OR" "LE" "LK"

[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$8:A$1000,A8)=1[/COLOR]

 

[patni]

Re: How to allow only certain alphanumeric data in a range

I tried this but something is wrong I guess. could you check please.
THANK YOU SO MUCH

[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",[/COLOR]LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0>=1RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$8:A$1000,A8)=1

 

[Rick Rothstein]

Re: How to allow only certain alphanumeric data in a range

Hey Rick Rothstein,
I just checked that.

Okay this is something intresting. Is there any ways to prevent this ?
Just very curious

The only way to have fool-proof data validation is by using properly designed Change event coding.

 

[Special-K99]

Re: How to allow only certain alphanumeric data in a range

I tried this but something is wrong I guess. could you check please.
THANK YOU SO MUCH

[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",[/COLOR]LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0>=1[color=red][b],[/b][/color]RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$8:A$1000,A8)=1

You've deleted a comma after the second RIGHT, see highlighted

 

[Special-K99]

Re: How to allow only certain alphanumeric data in a range

And you've deleted the closing brackets and changed the COUNTIF formula so it doesnt work
This is what it should be

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR")))),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$1:A$1000,A8)=1)

If you're gonna change cell references in this formula then only change the references to cell A8 - NO OTHER CELL REFERENCES or you're undoing the work I've done for you.

 

[patni]

Re: How to allow only certain alphanumeric data in a range

Hey Special K99 ,
Sure.
Thank you so much for the help.
I checked my mistake and hopefully i learn from it.

The code works perfectly now.
Thank you for the help

 

[patni]

Re: How to allow only certain alphanumeric data in a range

Hey Special K99,
I need help regarding this thread.
In the previous formulae we had allowed "LK" , "AK", "AE", OR" codes from 1 to 100

I want to add 3 more to this formulae. I want to add "NL" , "BA" , and "CH" (same as previous from 1 to 100)

Could you help me please. I am unable to crack it.
Thank you

And you've deleted the closing brackets and changed the COUNTIF formula so it doesnt work
This is what it should be

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR")))),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$1:A$1000,A8)=1)

If you're gonna change cell references in this formula then only change the references to cell A8 - NO OTHER CELL REFERENCES or you're undoing the work I've done for you.

 

[Special-K99]

Re: How to allow only certain alphanumeric data in a range

Try (untested)

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="AK",LEFT(A8,2)="OR",LEFT(A8,2)="NL",LEFT(A8,2)="BA",LEFT(A8,2)="CH"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$1:A$1000,A8)=1)

 

[patni]

Re: How to allow only certain alphanumeric data in a range

Hey Special K99,
thank you for the reply.

The above code is not working. I am not able to input any data as of now.
Whatever I type, excel data valididation stops me from typing.

Try (untested)

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="AK",LEFT(A8,2)="OR",LEFT(A8,2)="NL",LEFT(A8,2)="BA",LEFT(A8,2)="CH"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$1:A$1000,A8)=1)