Automating Data Entry with Data Validation Lists
Data Validation is one of Excel’s most underutilized tools from a data entry perspective, especially its ability to manage list type functions. What is Data Validation? Well it’s essentially a robust set of tools that allow you to define the parameters you want for certain data entry cells. In other words, you can control what users can & can’t enter in the cells that you define.
How many times do you have to enter some of the same information in a form, or run into situations where users butcher what should be normal entries, or they don’t enter data in a format you need? Have you ever wondered if you could come up with a simple solution that would make not only your life easier, but your users’ as well? With Data Validation you can fix almost all of that (although no matter what you do you can always count on a certain population to completely mess up what you’re trying to do).
NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.
The most common use of Data Validation is to populate a cell (or cells) with a list of pre-defined items. Starting with a fairly common example, let’s say you have an expense report with a cell for users to enter their office location. Instead of relying on manual entry (and errors), you can use Data Validation to create a list of locations for them to select instead (Figure 1). I like to add a “helper cell” either above or to the left of the data validation cell to point users to where they should be looking to enter data (in this case “Office Location”):
Setting up your list:
There are two methods of creating lists with Data Validation. The first is a manual method using the Data Validation dialog directly. This is preferable for smaller lists that won’t vary often, like “Yes/No/Maybe”, “Male/Female”, etc. To use this method, select the target cell (where you want the selection to be made), then goto the Data tab–>Data Validation (/avv). On the Settings tab, in the Allow box, select “List”, then in the Source box enter your values, separated by commas, but with no spaces in between. E.G. “Yes,No,Maybe” (Figure 2).
As you can see, this is fairly easy for a short list, but can become cumbersome for larger lists, especially as the nature of the source dialog box is to allow you to select a range on the worksheet, so if you try to use the Home, End or Arrow keys when you’re in the Source dialog box, all of a sudden you’ll find a cell range entered into your list items, and you’ll have to delete it in order for the list to be valid (Figure 2.1). This means that you have to manually select each item with the mouse if you want to replace it, or retype the list.
Note that the two check boxes you see (Ignore blank and In-cell dropdown) will be checked as their default positions, and in general when working with lists you won’t need or want to uncheck them.
Setting up Data Validation Lists
The second method for creating lists is to 1) enter the list in the same worksheet and refer to the range it occupies (Figure 3):
Or 2) the method I prefer is to use an unused worksheet (which can be hidden) and create a named range for the list. Enter your list on the sheet and goto the Formula tab–>Name–>Define (Figure 4):
Named range as a list source
Now you can go back to the Data Validation dialog and refer to the named range (Figure 5):
And you’ll get the same results as if you referred to a worksheet range (Figure 6):
Note: it’s not at all uncommon to forget the “=” when using named range references, but you’ll soon find out you forgot it when you select your data validated cell and your option is “MyList”. All you need to do is open the Data Validation dialog again and add the “=” to fix it.
The nice thing about using named ranges is that you can keep your lists away from your users, by adding a sheet for them, then hiding the sheet with goto the Home tab–>Cells–>Format–>Visibility–>Hide & Unhide). This is also a handy method if you need to maintain data sources hidden from users, like LOOKUP tables and other references.
User Input Message(s):
The second tab on the Data Validation Dialog is the Input Message, which will pop up a comment box on the target cell when it’s selected:
This is probably the easiest part of setting up Data Validation and you set it up through the Input Message tab:
There are two elements to the Input Message, the Title and the Message itself, both of which are pretty self explanatory. Note that there’s an option to show the message when the Data Validated cell is selected, which is pretty useless, as you don’t have to enter anything in there in the first place.
Error Alert Message(s):
The third tab on the Data Validation Dialog is the Error Alert Message, which will pop up a message box anytime a user tries to enter their own value instead of one that you have defined.
The entry for this is very similar to the Input Message:
The same goes for the check box to enable the Error Alert message check box as with the Input Message. If you don’t want to use those features, there’s not really a reason to check the box.
That’s pretty much it for the List feature in Data Validation, but I’ll be adding more stuff about Data Validation soon. If there’s anything you think I forgot in this post, or if you have any questions, please don’t hesitate to ask.
Smitty
What would be great is if you could use your Custom Lists as data validation. Otherwise you have to write them out in the data validation listbox, or type them onto the worksheet, in order to use them.
I’ll have to have a look into that. Custom List values are stored in the registry, so it may be possible to use an API call to access them.
In this Mr.Excel Message board post Microsoft Excel MVP Tom Urtis explains how to access Excel’s custom lists via Data Validation using VBA: link removed. See the code in the next comment.
When I click that link I get the following message:
JP2112, you do not have permission to access this page. This could be due to one of several reasons:
1. Your user account may not have sufficient privileges to access this page. Are you trying to edit someone else’s post, access administrative features or some other privileged system?
2. If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
My bad. Here is the code Tom provided, as well as some from Excel MVP Debra Dalgleish:
'===========================' Methods for accessing Custom Lists in the Registry
' To populate Data Validation Lists
' Microsoft Excel MVP Debra Dalgleish - http://www.contextures.com
‘ 03/29/09
Sub GetCustomLists()
‘extract custom lists from registry
Dim listArray As Variant
Dim iItems As Integer
Dim iLists As Integer
Dim ws As Worksheet
For iLists = 5 To Application.CustomListCount
listArray = Application.GetCustomListContents(iLists)
Worksheets.Add
Set ws = ActiveSheet
For iItems = LBound(listArray, 1) To UBound(listArray, 1)
ws.Cells(iItems, 1).Value = listArray(iItems)
Next iItems
Next iLists
End Sub
‘=======================================
‘ Methods for writng Data Validation Lists to Custom Lists in the Registry
‘ Microsoft Excel MVP Debra Dalgleish - http://www.contextures.com
‘ 03/29/09
Sub Custom_List()
‘add custom lists from active workbook to registry
Dim ws As Worksheet
Dim listArray As Variant
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Application.AddCustomList _
listArray:=ws.Range(”A1″).CurrentRegion
Next ws
End Sub
‘===========================
‘ Methods for accessing Custom Lists in the Registry
‘ To populate Data Validation Lists
‘ Microsoft Excel MVP Tom Urtis
‘ 03/29/09
Sub CustomListDV()
Dim strCustom$, i%, myCustomList As Variant
myCustomList = Application.GetCustomListContents(5)
For i = LBound(myCustomList) To UBound(myCustomList)
strCustom = strCustom & myCustomList(i) & “,”
Next i
strCustom = Mid(strCustom, 1, Len(strCustom) - 1)
With Range(”J3″).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strCustom
.ErrorTitle = “Invalid entry !”
.ErrorMessage = “Please enter an item” & Chr(10) & “from the drop-down list.”
.ShowError = True
End With
End Sub
‘====================
I’ll make this a stand-alone post of its own shortly.
Thanks for sharing. The last set of code looks closest to what I was asking about. Although it would be accessible to end users who don’t know VBA by just making it available directly in the Data Validation dialog box.
Also, wouldn’t it be easier to use the Join function to create strCustom, rather than a loop?
i.e. strCustom = Join(myCustomList, “,”)
I supoose to make it generaly accessible you’d need to create an add-in. As for the Join function, I think that may just be Tom’s personal coding preference.
Incredible site!
viagra I rarely comment on blogs but yours I had to stop and say Great Blog!!
[...] An added bonus to worksheet protection is that it establishes a tab order, so that you can move between unprotected cells in sequence with the Tab key. The order goes from left to right across a row, then down to the next unprotected cell in the next row. This can take some intuitive design so that you tab through cells in the order you want (how frustrating is it to fill out a web form where the tab order is out of sequence?) But once you have it set up right it can make life for your users so much easier. I also like to add Data Validation to protected sheets to make working with them even simpler (see: http://www.smittypro.com/Blog/?p=25). [...]
Two comments:
1. [There are two methods of creating lists with Data Validation. The first is a manual method using the Data Validation dialog directly. This is preferable for smaller lists that won’t vary often, like “Yes/No/Maybe”, “Male/Female”, etc. To use this method, select the target cell (where you want the selection to be made), then goto the Data tab–>Data Validation (/avv). On the Settings tab, in the Allow box, select “List”, then in the Source box enter your values, separated by commas, but with no spaces in between. E.G. “Yes,No,Maybe”]
One drawback of this method is that Excel will still allow certain invalid entries to be made. Entries that consist of one of the valid entries but preceeded and/or followed by any number of spaces will not be disallowed by the Data Validation.
For the examples given, the following ‘invalid’ entries (without the “”) can be made in the worksheets cells: ” No ” or “Male “.
This can make any subsequent lookups, countifs etc based on the supposedly validated entries unreliable.
2. [... so if you try to use the Home, End or Arrow keys when you’re in the Source dialog box, all of a sudden you’ll find a cell range entered into your list items, ...]
However, this can be avoided by pressing F2 before using the Home, End or Arrow keys.
Thanks for the comments Peter (a Mr.Excel MVP)…You learn something new with Excel everyday!
Hi, nice post. I have been pondering this issue,so thanks for writing. I will definitely be coming back to your posts.
Nice Post.
But one query ? How to add to already existing list? How to find where Mylist items are written if we want to add/delete items.
Any easier way
I’m using data validation on Excel 2007. I accidentally clicked on the Input Message. Now, rather than staying near the selected cell, all of the Input Messages stay in the same place on the screen. How can I reverse this?
Hi, this is good stuff there from you. Keep it up and post more on the topic.
The next logical step/question (for me at least) is: How do/can you use this method to have other cells auto-populate data (i.e. if I select Yes, plase a 1 in a cell, if I select No, place a 2 in that same cell)?
You can use an Array Lookup:
=LOOKUP(A1,{”No”,”Yes”},{2,1})
Where A1 houses the validation list.
Hope that helps,
[...] — read Smitty’s Automating Data Entry with Data Validation Lists [...]
[...] Automating Data Entry and Protecting your Data How many times have you sent out a workbook for users to fill in data, only to have them delete or somehow screw up your formulas? Or have a complete workbook that has a lot of formulas dependent on data entry and you accidentally overwrite or delete an essential formula? I’ve done it many times, and I hate recreating perfectly good work. I’m also a huge proponent of automating work. Case in point: I standardized a corporate expense report (previously each office had their own version) and got approval from HR, IS and Payroll/Accounts Payable for implementing it. On the day before it was supposed to be released, the head of Accounts Payable called me and said that she couldn’t send it out, because she couldn’t change the date; I told her that’s because it was protected. She replied that it was “stuck” on 12/13 and she needed to be able to change the date. I explained that it was protected because there was a formula there (=TODAY()), and that tomorrow the date would be 12/14. That literally made her head spin (both that I’d had the foresight to add the formula, which she’d never seen, and protect the worksheet). It’s such a simple thing, but so few people seem to know about it, when in fact, it’s deceptively easy. This is where worksheet protection comes in…Did you know that you can use worksheet protection to allow you and your users to interact with your workbook, but not let them alter any cells with formulas? An added bonus to worksheet protection is that it establishes a tab order, so that you can move between unprotected cells in sequence with the Tab key. The order goes from left to right across a row, then down to the next unprotected cell in the next row. This can take some intuitive design so that you tab through cells in the order you want (how frustrating is it to fill out a web form where the tab order is out of sequence?) But once you have it set up right it can make life for your users so much easier. I also like to add Data Validation to protected sheets to make working with them even simpler (see: http://www.smittypro.com/Blog/?p=25). [...]
I rarely comment on blogs but yours I had to stop and say Great Blog!!
This is a very good information related to data entry and data validation
cross-linkz
Can validation list source be a table column?
=Series_Tab[[#Data],[Series Name]]
gives me a syntax error. The same formula in a cell does not (just a #VALUE error since the source is a range, not a single cell).
Thanks!
Forgot to mention it’s Excel 2007.
Also, this formula works in a cell:
=VLOOKUP(”test”,Series_Tab[[#Data],[Series Name]],1)
proving that the table ref syntax itself is correct…
I haven’t worked with Tables much (I haven’t found them flexible enough for my needs), so I’d have to do some research and see what I can find.
Thanks - I’m trying to learn how to use them but it’s very frustrating and maybe not that much of an improvement over zipping around with range names and the OFFSET function. Tables have the potential for better row integrity though. I should mention that my application would be a breeze in Access but I’m too cheap to buy it…
Give the Mr. Excel forum a shot with the question (www.mrexcel.com/board2), as I’ll bet someone over there will know the answer. As to this being easier in Access, that’s where I keep my larger datasets, hence my lack of table use. If you can afford it at some point, it’s a great tool.
When I retired and gave back my company-imaged laptop I had a ton of personal Excel and Word files but just work-related stuff in Access, so I just got Office Home & Student ($75 vs. $260 for the cheapest one with Access). Eventually I plan to try the OpenOffice database component, and maybe a combination of MySQL and Python.
Excel has its drawbacks - a careless insert or delete can permanently destroy 1000’s of rows of data. But it has advantages too - in my app I paste in gobs of messy raw data from multiple web sources that has to be parsed & cleansed. Even with Access I would probably still do some of that in Excel…,
There’s no doubt that used correctly both Excel and Access can go hand in hand. I haven’t much experience with MySQL, as for bigger DB’s I use SQL Server. But I do tend to stick to Excel where possible.
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.
I have a master sheet with employee names and what safety tickets they hold. I have multiple worksheets for different job sites. I have created a drop down list on the other worksheets. But what I am trying to do is have the information that is entered under the employee name automatically fill in the row when I choose an employee name from the drop down list. Please help…thnx
Check out Excel MVP Debra Dalgleish’s VLOOKUP tutorial: http://www.contextures.com/xlFunctions02.html
It should be just what you’re after. Smitty
Was curious, if we can select multiple values for data validation, that is, If i assign a task to two people, from the drop down list, I can select two people, instead of one.
BTW, its a very good blog, Half way through, I could pick, what I was looking for.
Thanks,
Durga PK Saitana
Not with Data Validation, but you can use a List Box and set its multi select property to True.
Is there any way to select more than one item in a drop down list to populate a field- seperated by commas if that isn’t asking too much!
I was trying to validate a column so that it accept only “Dates” . the date format I am using is dd/mm/yyyy and not mm/dd/yyyy. can you explain to me how to validate a column so that it accept only date format “dd/mm/yyyy”
Thank you
Can you format a cell with a validation rule to show the drop down arrow even when the cell is not selected??
lists with data validiation are great.
My hassle at the moment is we have a drop down list of 200 items, from a data entry perspective in a worksheet we cannot start typing the name and the field self populates the rest of the information,
any ideas on how this can be done
If you use a ComboBox from the Control Toolbox you’ll get that functionality. Just put your Data Validation range in the ComboBox’s ListFill range and you’ll be good to go.
Nope. For that you’ll need to use a ComboBox from either the Forms toolbar or the Control Toolbox.
You’re probably better off using the Date/Time Picker Control. That way you don’t need to worry about validation and it’s a web type interface most users are comfortable with. See Martin Green’s http://www.fontstuff.com for a great tutorial on it.
You can use a ListBox control, but getting the display you want would probably require VBA.
This link may help: http://www.bigresource.com/VB-Multiple-selection-in-dropdown-control-SQwm8oab8D.html