formula for specified data validation required

**jarssonn** · 11-18-2010, 04:22 AM

Hi all excel experts!
I am trying to apply data validation for cells E7:E1006.
I would like to have a rule that only following format will be possible to input in those cells:
4-digit number and a small letter (i.e. 1234a, 2345b, 6666d).
Unfortunately I have no idea what formula I should use in data validation.
Any help much appreciated.
Thanks in advance!

**DonkeyOte** · 11-18-2010, 04:27 AM

With E7:E1006 active - having selected E7 first - you could try a Custom DV Formula of:

Please Login or Register  to view this content.

**sweep** · 11-18-2010, 04:31 AM

Hi,

Does this work for you?

Please Login or Register  to view this content.

**DonkeyOte** · 11-18-2010, 04:33 AM

I should add that my suggestion is flawed should 0000 be valid
To account for that you would need to use an ISNUMBER test rather than rely on coercion (coercion of 0000 would lead ultimately to a FALSE result)

**jarssonn** · 11-18-2010, 04:39 AM

Works as it should, thank you very much!!!
Just to get some knowledge how it works so next time maybe I will be able to adapt this into my needs:
which part determines what?
I suppose =(LEN($E7)=5) tells that format of input will have 5 characters, but I have no idea about the rest... what number 97 and 122 are there for?
Thanks for patience, code works perfectly I am just person which does not like to get something done by somebody else and use it if I do not understand how it works...

**DonkeyOte** · 11-18-2010, 04:46 AM

"a" is Char 97
"z" is Char 122
CODE function is used to determine the CHAR # of a given character

If the returned value sits outside of the 97-122 boundaries you know it's not lower case a-z

**jarssonn** · 11-18-2010, 04:57 AM

Thanks for explanation. Just wondering about something else. Is there possibility to modify it so it is still requiring following code format (i.e. 1234a - 4-digit and small letter) but this code can be used only if is on the list which is located in range A3:A in tab called 'LOOKUP' (I can't give end of the codes range as it will grow as new codes are added).
Hope I was clear enough in explaining this...

**DonkeyOte** · 11-18-2010, 05:25 AM

If it transpires that LOOKUP!A3:An includes only valid entries then just apply a MATCH test regards the DV:

Please Login or Register  to view this content.

where _DVList is a Dynamic Named Range defined as:

Please Login or Register  to view this content.

which will resize as records are added/removed from the listing

Note: LOOKUP is a function - it's best to avoid naming sheets the same as functions etc for sake of clarity - would suggest renaming to LOOKUPS or some such.