validation formula for text length, case and dependent on value of another field

[ssanders]

I have a situation where I need to create a validation formula that limits length of entry to 30 characters or less, is all upper case and the end of the entry be the letters "ET" if the value of another cell is equal to "Y".

I have already worked out the formula for the combination of length and case to be:

=AND(LEN(V4)<=30,EXACT(V4,UPPER(V4)))

where V4 the cell the validation is for but not sure how to add the part about forcing the last two letters of the entry to be ET if the value of cell K4 is Y.

Any suggestions as to whether this can be done? Thanks in advance for any suggestions.

 

[Snakehips]

ssanders,

Welcome to MrExcel.

Two possibilities....

Excel 2007
	Y
4	TRUE
5	TRUE
Sheet9

Cell Formulas
Range	Formula
Y4	=AND(LEN(V4)<=30,EXACT(V4,UPPER(V4)),(RIGHT(V4,2)="ET")=(K4="Y"))
Y5	=AND(LEN(V4)<=30,EXACT(V4,UPPER(V4)),IF(AND(K4="Y",RIGHT(V4,2)<>"ET"),FALSE,TRUE))

The first one will not accept ET as the end of the input string if K4 is NOT "Y"

The second will accept "ET" on the end of the sting in all cases.

Hope that helps.

 

[ssanders]

Thanks for the suggestion. I guess, I need to be a bit more clear in the description of what I need. If the value in K4 is Y, then I want to force the string entered in V4 to end in ET. If the value in K4 is N (the other option), then it is OK for V4 to end in ET. What I am looking to do is force the user to end the string in ET if K4 is equal to Y. Can that be done?

 

[Snakehips]

That is exactly what my second formula should be doing.
How is it not working for you?

 

[ssanders]

I must have had an error in the way I had entered it originally because it seemed to allow anything whether the value in K4 was Y or N. Seems to be working now. Thanks so much for your help.