In-cell dropdown by Data Validation – Allow input NOT from a list

This is about a trick of using named range that includes a blank cell; which allows any input even the cell is with Data Validation that allows list.

Excel Tips - Input anything in cell with list data validation 1

When you attempt to input anything other than what is restricted by Data Validation, you will probably see the screen shot above which leaves you basically no choice but “Retry”. Actually there are two ways to get rid of this annoying message and input anything to a cell with data validation.  Let’s talk about the conventional way first.

A) By setting appropriate Error Alert

The screenshot below shows how to set the data validation from a predefined list.  To to that, go to

  • Data Tab–> Data Validation –> On Setting Tab
  • Allow: List ; Sources: “= $D$1:$D$5” where holds the predefined list that we allow:

Excel Tips - Input anything in cell with list data validation 2

Now go to Error Alert tab.  By default, the Style is set as “Stop” which does not allow user to input anything else.  Nevertheless we have two more options: “Warning” and “Information“.

Excel Tips - Input anything in cell with list data validation 3

Below is what you see when invalid data is input under different Error Styles selected:

  1. Stop – You have no choice but “Retry”.  “Cancel” returns original value of the cell.Excel Tips - Input anything in cell with list data validation 4
  2. Warning – You will be prompted for inputting invalid data, with a chance to proceed with the “invalid” input by pressing “Yes”.  “No” –> Input again.Excel Tips - Input anything in cell with list data validation 5
  3. Information – You will still be prompted; and “invalid” input will be accepted by pressing “OK”.  Pressing “Cancel” returns original value of the cell.Excel Tips - Input anything in cell with list data validation 6

So to allow “invalid” input, select either “Warning“or “Information” in the Error Alert tab.  (Tips: you may customized the Alert Message in “Error message” box.)

To get rid of the error message without prompt, uncheck the check box as shown below:

Excel Tips - Input anything in cell with list data validation 7

You can input anything with this setting.

Excel Tips - Input anything in cell with list data validation 10

B) By defining Name to the predefined list; and then apply Name to Data Validation

The trick is to include an empty cell in the range when defining the Name.  See below:

Excel Tips - Input anything in cell with list data validation 8(Shortcut: CTRL + F3 to open the Name Manger)

Now we can apply the Data Validation using the name “List” just defined.  Make sure the “Ignore blank” is checked.

Excel Tips - Input anything in cell with list data validation 11

Anything can be input even we did not change the default Error Alert setting.

Excel Tips - Input anything in cell with list data validation 10

Note: Interestingly, applying the range $D$1:$D$6 directly to the “Source:” does not give you the freedom; defining a Name to $D$1:$D$6 and then applying the Name does.  Put it in other words, it seems that the “Ignore blank” option only works for named range.

Final note

It is actually paradoxical to allow anything be input in a cell with data validation.

The idea of using Data Validation is to prevent user from inputting whatever they want. This is quite important for data analysis because no one wants to work with a messy data base that contains a wide variety of input for the same thing, e.g. Jan, January or J.

However in some situations, a “form” is created using Excel for data collection, e.g. a questionnaire.  Data validation is used to create a drop down list to facilitate input.  Nevertheless we still want to collect information other than the list provided.  Thus the trick just discussed maybe helpful.

Think carefully about what you really want to do! 🙂

Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

6 Responses to In-cell dropdown by Data Validation – Allow input NOT from a list

  1. MF says:

    Glad it helps.

    Like

  2. Vivek says:

    WOW!! I had looked and saw the various options but did not realize/test that they functioned differently. I thought all I was changing was the icon and therefore the severity of my message. I was ready to start writing som4e heavy duty VBA code. LOL

    Like

  3. XOR LX says:

    Completely agree. It’s strange how often this is the case. And yet all that it takes is a few minutes to play about with some of these other options/parameters, if not just to see what they actually do!

    Seems like the majority of us are unfortunately hard-wired to ignore what we consider “extraneous” information, even if, paradoxically, that information may actually be of use to us.

    Keep up the good work.

    Cheers

    Liked by 1 person

    • MF says:

      Exactly!
      And the worst part is some people keep complaining the software is “stupid” because they do not care do learn how to use it properly… ~_~
      You too keep up the good work!
      Cheers,

      Like

  4. XOR LX says:

    Nicely explained. I’d imagine there’d be quite a few people who weren’t aware of these options, so perhaps your post will help prevent some validation-related headaches!

    Cheers

    Like

    • MF says:

      Thanks XOR LX for your kind words! 🙂
      Yes I agree with you. In my observation, many users do not care to look at different options offered in various built-in tools in Excel; and most users have an “instant” response to CLOSE error message without reading the message at all. Actually, they are not rocket science. They are just being ignored.

      Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s