Two types of Data Validation in a cell?

  • Thread starter Thread starter Lakeville
  • Start date Start date
L

Lakeville

I want to ensure that the data in a cell is either a capitalized Y or a
capitalized N. I understand how to do the data validation of a list with Y &
N. And I understand how to enforce capitalization: =EXACT(A1,UPPER(A1)).
However, I don't understand how to enforce both for the same cell.
 
You could just select the 'List' option and type Y,N in the criteria. It is
case sensitive.
 
Data > Valivation > List > Source = =$B$1:$B$2
In Cell B1, put Y and in Cell B2, put N
 
I thought it was case sensitive, but it is not working that way for me.

:
You could just select the 'List' option and type Y,N in the criteria. It is
case sensitive.
 
This is what I already have. If the user uses the drop down to select Y or N,
then it works properly. However, a user can manually enter lowercase y or n,
and I want to avoid that.
 
Strange. It would only stop them from entering the text once they navigate
away from the cell, and only if 'Show error alert after invalid data is
entered' is checked in the Error Alert tab of the Data Validation menu form.

Biff's solution should work too.
 
Or, the robust version:
=FIND(A1,UPPER("~YN"))>1
Click to expand...

your formula fail. If user enter both YN in a cell


Try this one:

=AND(EXACT(A1,UPPER(A1)),OR(A1="Y",A1="N"))
 
=FIND(A1,UPPER("~YN"))>1
your formula fail. If user enter both YN in a cell
Click to expand...

Good catch. I didn't test for that possibility.

Try this one:

=(LEN(A1)=1)*(FIND(A1,UPPER("YN")))
 
Back
Top