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
WeThotUWasAToadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.