Conditional format

[Livin404]

Good afternoon I need a little help with conditional formatting. I’m using it to identify errors before its uploaded into a large database (not Excel) I have a column where last name, first name, middle name and suffix will need to be entered. I already have it highlight punctuation marks, numbers, and spaces before or after the last name, first etc. ( none of those can be used). I used =LEN($A2)>LEN(TRIM($A2)). To flag spaces. In short. with the cell there are four blocks of information in one cell, last name, first name, and middle initial and suffix. I need the cell to highlight if there is no information after the first name.
For example my full name is:
Whiteside Sheridan Ulysses III.
In the cell I need to highlight if I only enter Whiteside Sheridan.

Thank you,

 

[sparky2205]

I'm sorry but I still don't understand this.
I know what you're trying to achieve but I'm taking mixed messages form your posts regarding the relationship between Service and Rank.
Are the green Service & Rank headings on sheet 1 and the other colour A, F & N headings on sheet 2?
If the Rank is not duplicated then why is the same Rank associated with more than one letter e.g. MAJ is under both letters A and F in columns F and G.

 

[Livin404]

I'm sorry but I still don't understand this.
I know what you're trying to achieve but I'm taking mixed messages form your posts regarding the relationship between Service and Rank.
Are the green Service & Rank headings on sheet 1 and the other colour A, F & N headings on sheet 2?
If the Rank is not duplicated then why is the same Rank associated with more than one letter e.g. MAJ is under both letters A and F in columns F and G.

Yes sheet 1 is for the service and rank, sheet 2 has the listings. Sheet 2 will be hidden and out of view. Yes to your second question some ranks are the same in the services. I would want the cell to highlight if a wrong rank were put into a service like SRA put in for N ( Navy). Obviously highlight if something like Capt. were put in while the letter F were placed in for service, I could have a separate rule for each service if that makes it easier.

thank you and no need to apologize, you’re the one helping me out.

 

[Livin404]

Hey thank you for the effort, it's all sorted out now. The code I need is =ISERROR(MATCH(D2,CHOOSE(SEARCH(J2,"ACDFGJMNX"),ARMY,CoastGuard,DOD,AirForce,USFED,JointCommand,Marines,Navy,Foreign),0)). It works pefectly. Many thanks again.

 

[Livin404]

This will work under the strict condition that there is NO information after the first name, which is what you requested:
=ISERROR(FIND(" ",A2,FIND(" ",A2)+1))
[/QUOTE]

Hey how are you doing, this project I've been working on is driving me mad. Earlier in the week you help me with coding to detect any extra spaces in the cell, and that has been gold, thank you. There is one last thing in that first column I need to wrap up.

I need to prevent anyone going out further than a Suffix in a name. For example my name would be Whiteside Sheridan Ulysses III. Everything I need in the cell is exactly the way I need it, with the exception of someone trying to add a fifth block of details. I just need it to error out if someone were to add on additional block of letters. I hope that makes sense. Thank you so much and have a good weekend.

 

[sparky2205]

Good afternoon, glad you got your other issue sorted.
What about this for the request above? =LEN(A5)-LEN(SUBSTITUTE(A5," ",""))>3,
It checks for a fourth space in the data.

 

[Livin404]

It worked great thank you!