Subscribe
I want a validation on a single column in which only email id can be entered or a group name from the list of groups can be entered. i have no idea how to do it. I have a name list named groups that contain my group name.
Two validations on a single column.
(6 posts) (4 voices)-
Posted 8 months ago #
-
Hi Bhargava,
The following should work for e-mail address validation assuming user will enter e-mail address as abc.xyz@efg.com (format example).
=AND( FIND(".",A1),FIND("@",A1))
[put it in the 'formula' under 'custom' from 'allow'; make sure you select the cell, which you want to be validated, before putting the formula]
Say, you have validated cell A1, now put anything other than the format mentioned above, excel should throw you an error message.
Can u plz explain what groups you are talking about? Is there any specific format which needs to be followed when user would enter the group name?
Regards,
KaushikPosted 8 months ago # -
KAUSHIK, THANKS FOR THE REPLY.
I HAVE CREATED A LIST HAVING A,B,C AND THE NAME OF THE LIST IS LETTERS
THERE IS A COLUMN "A" CAN HAVE EMAIL ID AND IT CAN ALSO HAVE ANY VALUE FROM THE LIST LETTERS.
I HAVE TO APPLY VALIDATION TO THE COLUMN THAT THE USER CAN ENTER EMAIL ID OR ANY VALUE FROM THE LIST LETTERS.Posted 8 months ago # -
Select Column A
1. Data Mune
2. Data Tools
3. Data Validation
4. Allow > Custom
5. Write the name of the list in the formula box i.e =LETTERS
6. oK
Done !Posted 8 months ago # -
Nazmul
it is not simple buddy, there are 50 entries in the list letters and the list can also change in future. And the column can contain email id too.
Posted 8 months ago # -
Kush,
If you want to limit in a cell (say, A2) the input to a list, custom Data Validation formula is:
=OR(ISNUMBER(MATCH(A2,Groups,0)),ISNUMBER(MATCH(A2,Letters,0)))I would recommend making the named range "Letters" dynamic so that as you add/delete entries from it, the formula does not need to be updated. Formula will only allow user to input a value that is found in the list. It does NOT give the user a dropdown.
Posted 8 months ago #
Reply
You must log in to post.
Welcome to Chandoo.org. New here?
Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.
