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

#### JPhotonics

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

#### JPhotonics

##### Member
You only have to uncheck "Ignore blank" in validation settings.

View attachment 65441
I want to key off of A column. If there is data in A2, there must be an entry in J2 before entering anything in the next row - A3, A4, etc.

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

#### JPhotonics

##### Member
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).

#### JPhotonics

##### Member
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.