Function to a value in check a list

[singhabhijitkumar]

I have created a list for Cells A2:A10 which has the various roles of person. Gave it a name Designation. I have created a drop down list using this list by going to validation. =Designation

I want a function that would check the value entered and search this list would return TRUE or FALSE

[teylyn]

search this list based on what? If you picked the value from a data validation list, what do you want to check ??

[singhabhijitkumar]

sometimes users can copy-paste data over a cell having a a drop down list. So the value gets accepted. I want to write a VBA code that would loop through a column and check if the values in the column are as per the list i created.

[teylyn]

Where would you want that TRUE or FALSE to be? This can be done without VBA.

put this in B2 and copy down.

=IF(ISBLANK(A2),"",IF(NOT(ISNA(MATCH(A2,designation,0))),"true","false"))

[singhabhijitkumar]

hi, i have a lot of validations in my worksheet. This is just one of them.
I am supposed to create a button on the worksheet. On the press of this button the various validation failures should be listed on a new worksheet.

So i am looking for a function which would check if the data in a field is as per the list. If not return FALSE. The list is defined in another worksheet.

[teylyn]

singhabhijitkumar, please don't quote whole posts. It just clutters up the forum.

It helps if you specify your complete requirements first time round, then the suggestions can be more targeted to your needs.

How would you want the macro to work with regards to finding the validation cells and which validation lists they relate to? Should it locate them automatically, or will you provide a list of cells to check on another sheet?

Maybe you could post a sample workbook with your data layout?

[singhabhijitkumar]

Hi,
I have list named Gender with values M and F. This list is present in a different worksheet name Lists. I have defined a Label (Insert->Name->Label). Now I am using this Label to create a list in column D of my Data worksheet (=Gender).
I need a function. The function would be passed the values in column D of my Data worksheet. It should check if the value belongs to the values in the list Gender. If not it should return FALSE.

[teylyn]

Sorry, but I am confused. First you talk about one validation range. In post #5 you say you want to check multiple validation ranges, now you say again you want to check only one validation range.

How about you post your spreadsheet, or a workbook that has the same structure, but uses dummy data. Then point out which validation ranges you would like checked, where you would want the results of these checks to appear and what you would like to see. Just showing "FALSE" on another worksheet won't help you much, unless you also see which cell the "FALSE" relates to. So, do you want to see the address of the cell, the value of the cell??

[singhabhijitkumar]

It has multiple validation of different kind, like length, not null, list validation. For list validation, one of the list if for Gender. For this I need a function. I will loop through my data and pass this function one value at a time. This function should refer the list defined in another worksheet and check if the value passed is present in the list. If not return FALSE.
How do i post my XLS here?

[teylyn]

You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

[singhabhijitkumar]

the list is present in hidden worksheet Lists and the list is being used in column D of Data worksheet.
I want modify the line "Trim(cellval) <> "M" And Trim(cellval) <> "F" in the code. I want to use a function to which i would pass the cellvalue and the list name. It should check the list for the presence of the value. If not present return FALSE

[mikerickson]

If a cell has List Validation and its set to Locked, when the sheet is protected, the user will be able to change the selection with the dropdown, but can't paste over the cell.

[singhabhijitkumar]

yes, but in this case if the users want to delete a row they cannot.
and if locked an protected users won't be able to change the values either

[singhabhijitkumar]

hi Teylyn, any updates. can i use my LABELS to check whether a cell value is present in the list created using a label?

[teylyn]

If a cell has List Validation and its set to Locked, when the sheet is protected, the user will be able to change the selection with the dropdown, but can't paste over the cell.

I can't verify that. A locked cell with list validation can not be changed when the sheet is protected (tested in XL 2010). Can you explain how to achieve what you describe?

[mikerickson]

I can't verify that. A locked cell with list validation can not be changed when the sheet is protected (tested in XL 2010). Can you explain how to achieve what you describe?

In Excel 2004,
I selected C1 and set Validation to List with the source A1:A10. I verified that C1 is Locked

After I set Sheet protection, I was able to change C1 by selecting from the list.
When I tried to type an entry into the C1, rather than selecting form the list, the Sheet Protected message prevented me from entering the data.

It must be a special feature of 2004.

[teylyn]

Looks like it , because it does not work like that in 2010