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)))
Thank you in advance!
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!