| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two types of Data Validation in a cell?
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
You could just select the 'List' option and type Y,N in the criteria. It is
case sensitive. "Lakeville" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
Data Valivation List Source = =$B$1:$B$2
In Cell B1, put Y and in Cell B2, put N -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lakeville" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
Try this...
=FIND(A1,"~YN")1 Or, the robust version: =FIND(A1,UPPER("~YN"))1 -- Biff Microsoft Excel MVP "Lakeville" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
I thought it was case sensitive, but it is not working that way for me.
"Basil" wrote: You could just select the 'List' option and type Y,N in the criteria. It is case sensitive. "Lakeville" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
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. "ryguy7272" wrote: Data Valivation List Source = =$B$1:$B$2 In Cell B1, put Y and in Cell B2, put N -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lakeville" wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
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. "Lakeville" wrote: I thought it was case sensitive, but it is not working that way for me. "Basil" wrote: You could just select the 'List' option and type Y,N in the criteria. It is case sensitive. "Lakeville" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
Or, the robust version:
=FIND(A1,UPPER("~YN"))1 your formula fail. If user enter both YN in a cell Try this one: =AND(EXACT(A1,UPPER(A1)),OR(A1="Y",A1="N")) "T. Valko" wrote: Try this... =FIND(A1,"~YN")1 Or, the robust version: =FIND(A1,UPPER("~YN"))1 -- Biff Microsoft Excel MVP "Lakeville" wrote in message ... 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. . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two types of Data Validation in a cell?
=FIND(A1,UPPER("~YN"))1
your formula fail. If user enter both YN in a cell Good catch. I didn't test for that possibility. Try this one: =(LEN(A1)=1)*(FIND(A1,UPPER("YN"))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Or, the robust version: =FIND(A1,UPPER("~YN"))1 your formula fail. If user enter both YN in a cell Try this one: =AND(EXACT(A1,UPPER(A1)),OR(A1="Y",A1="N")) "T. Valko" wrote: Try this... =FIND(A1,"~YN")1 Or, the robust version: =FIND(A1,UPPER("~YN"))1 -- Biff Microsoft Excel MVP "Lakeville" wrote in message ... 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. . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Strange.
If you apply the list differently then it will not allow small case one. Use comma separated values in Validation List Source.
On Friday, January 08, 2010 12:55 PM Lakeville wrote: 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 do not understand how to enforce both for the same cell. On Friday, January 08, 2010 1:14 PM Basil wrote: You could just select the 'List' option and type Y,N in the criteria. It is case sensitive. "Lakeville" wrote: On Friday, January 08, 2010 1:15 PM ryguy7272 wrote: Data Valivation List Source = =$B$1:$B$2 In Cell B1, put Y and in Cell B2, put N -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lakeville" wrote: On Friday, January 08, 2010 1:15 PM T. Valko wrote: Try this... =FIND(A1,"~YN")1 Or, the robust version: =FIND(A1,UPPER("~YN"))1 -- Biff Microsoft Excel MVP On Friday, January 08, 2010 1:24 PM Lakeville wrote: I thought it was case sensitive, but it is not working that way for me. "Basil" wrote: You could just select the 'List' option and type Y,N in the criteria. It is case sensitive. On Friday, January 08, 2010 1:30 PM Lakeville wrote: 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. "ryguy7272" wrote: On Friday, January 08, 2010 2:14 PM Basil wrote: 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. "Lakeville" wrote: On Friday, January 08, 2010 11:46 PM Teethless mama wrote: your formula fail. If user enter both YN in a cell Try this one: =AND(EXACT(A1,UPPER(A1)),OR(A1="Y",A1="N")) "T. Valko" wrote: On Saturday, January 09, 2010 12:27 AM T. Valko wrote: Good catch. I did not test for that possibility. Try this one: =(LEN(A1)=1)*(FIND(A1,UPPER("YN"))) -- Biff Microsoft Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
| How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
| Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
| Data types when importing Excel data to SQLServer | Excel Discussion (Misc queries) | |||
| Mixed data types in a cell | Excel Discussion (Misc queries) | |||
Linear Mode
