• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation - Combining a 5-Digit Number Value Req. and Not Allowing Null Value

Hi folks!

I am a bit stuck here...
Trying to combine some data validation here in my Excel form.
I am able to get the error message working to force a 5-digit number to be used. However, I am having trouble also forcing an error/error message if/when the users skips the line when there is data in another cell.

The 1st Cell is A2 where someone would enter the initial information (Part Number). Cell J2 is where someone would enter the corresponding account number (5-digits).
What I need to do in Column J is:
1.) Required to be a number - complete
2.) Required to be 5-digits in length - complete
3.) If A2 has a value, J2 cannot be NULL (blank) when moving onto A3, A4, etc. - broken

The first part I solved with this code:
=OR(AND(LEN(J2)=5,ISNUMBER(J2)))
^^ This forces the entry to be a number and 5-digits long.

I was trying to combine using a COUNTIF, but didn't get it to work. I had tried this (both ways), but it failed:
=OR(AND(LEN(J2)=5,ISNUMBER(J2)))+COUNTIF($A$2:$J2,"")=0
=COUNTIF($A$2:$J2,"")=0+OR(AND(LEN(J2)=5,ISNUMBER(J2)))

Thank you in advance!
 

Chihiro

Excel Ninja
So you want validation on A3, A4 etc? That's separate validation from those on J2.
Validation should go on A column then.

In A3: Uncheck Ignore blank.
=NOT(ISBLANK(J2))
 
So you want validation on A3, A4 etc? That's separate validation from those on J2.
Validation should go on A column then.

In A3: Uncheck Ignore blank.
=NOT(ISBLANK(J2))
The issue there is I already have a Data Translation in Column A to provide a warning if the text entered is:
A.) Longer than 28 characters
B.) Use of any lowercase letter

I don't think I can do both, right? Well, maybe I can and maybe just the error message/warning would be OK.

Here is what's in there now:
=AND(EXACT(A2,UPPER(A2)),LEN(A2)<=28)
 

Chihiro

Excel Ninja
You can combine it. But I don't like using too many Data validation on sheets.

It can be easily be broken by user, simply by copying and pasting. I prefer to use Userform for data entry and validation (if I have to use Excel for data entry).
 
You can combine it. But I don't like using too many Data validation on sheets.

It can be easily be broken by user, simply by copying and pasting. I prefer to use Userform for data entry and validation (if I have to use Excel for data entry).
I'm not sure what you mean by Userform. I do need to use Excel as we're using a macro to then output the data in the format required to be uploaded into another program.
 
Top