• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Valid vs. invalid input in data-validated cells in Excel

Hello,

How do you specify valid vs. invalid input when using Data Validation to create a drop-down menu in Excel (2013)?

For example, suppose you have a spreadsheet in which the range $C$5:$C$99 (named "Vendors") contains the names of several dozen vendors.

Then suppose after selecting a range of cells in a different column (intended to have drop-down menus), you input the following:

        Data Validation > Settings > Allow > "List"
        Data Validation > Settings > Source > "=Vendors"

At this point, how can you limit user-entries (in the cells with drop-down menus) to only the names shown in the drop-down list?

Also, under the 3rd tab (Error Alert) in the Data Validation window is a checkbox titled:

        "Show error alert after invalid data is entered"

but how do you know or define what constitutes valid vs. invalid data?

Thanks
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
  • 2
  • 2
2 Solutions
 
Saurabh Singh TeotiaCommented:
First of all when you give a name range which is refering to range it will come without the double quotes sign..

What i meant to say you will simply say =vendors

Refer to enclosed file for example...

Once you corrected it then the validation will accept only the values from the list and if you try to enter values which is not their in the list, It will give you an error...

Now the 3 Kind of error messages are:-

1. Stop message:- It doesn't accept the data at all and asks you to re-enter values.
2. Warning Message:- It accepts the value with an error message which asking you what you want to do..Just in case if you want to accept the value and continue.
3. Information Message:- It will accept the value, However just give you an prompt that value which enter doesn't meets criteria of the validation.

Also please note normally when you apply data validation it won't accept any values which are not their on the list..However someone can still do a pastespecial->values their and it will accept those values even not their on validation...

Saurabh...
datavalidation.xlsx
0
 
Martin LissOlder than dirtCommented:
It will automatically be invalid if it's not in the Vendors named range.
0
 
WeThotUWasAToadAuthor Commented:
Many thanks for the responses.

the validation will accept only the values from the list
It will automatically be invalid if it's not in the Vendors named range.
I'm not sure what the problem was earlier (when I submitted this question) but I went back into the spreadsheet a few minutes ago and its functioning properly (ie just as I had thought it should and just as both of you said). :P

------------------------------------------------------------
when you give a name range which is refering to range it will come without the double quotes sign
I am aware that quotation marks should not be included and I apologize for the confusion in my question. I included them simply to indicate the selected ("List") or typed ("=Vendors") entries. But thank you for pointing that out.

------------------------------------------------------------
3 Kind of error messages
The optional error messages is something I was not familiar with so I appreciate you bringing that to my attention.
0
 
WeThotUWasAToadAuthor Commented:
Thanks
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now