SmittyPro - Witticisms, Help, Tutorials and More
Mar
23

Automating Data Entry with Data Validation Lists

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

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”):

Figure 1

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

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):

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

Named range as a list source

Now you can go back to the Data Validation dialog and refer to the named range (Figure 5):

Figure 5

And you’ll get the same results as if you referred to a worksheet range (Figure 6):

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:

figure-7

This is probably the easiest part of setting up Data Validation and you set it up through the Input Message tab:

figure-8

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.

figure-10

The entry for this is very similar to the Input Message:

figure-9

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

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

143 Responses to “Automating Data Entry with Data Validation Lists”

  1. I have 7 groups of items I need to connect in drop-down menues without having all of the groups listed in the first menue. Groups 1,2,3 are the anitial parts, the other 4 are specific options for the original selection?

  2. Pls. tell me about how to use the background color of list for validation in the area where it is validated?

    • admin

      It sounds like you’re after Conditional Formatting, which will change cell formatting if a certain condition is met. The helpfile has a good tutorial on it, but here’s a primmer.

      Let’s say you want a row to turn green if the value in column A is “Yes” (it can be a validation selection). Select the entire range of cells that you want to apply the formatting to and goto Format–>Conditional Formatting–>Formula Is–>=$A1=”Yes”–>Apply the format you want. In 2007+ you’d goto Home–>Conditional Formatting–>New Rule–>Use a formula to determine which cells to format.

  3. How do I add a dropdown list to userform using Visual Basic in Excel????? I get the list to appear, but I can’t pick only one choice.

    • admin

      Here’s an example populating a ComboBox and a ListBox with data in Sheet1 A1:A20:

      Private Sub UserForm_Initialize()
      Dim i As Long

      ' Populate ComboBox
      For i = 1 To 20
      UserForm1.ComboBox1.AddItem Sheets("Sheet1").Cells(i, "A")
      Next i

      ' Populate ListBox
      For i = 1 To 20
      UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(i, "A")
      Next i

      End Sub

      If you want the ability to multi-select, you’ll need to use the ListBox and in its properties goto MultiSelect–>1 – MultiSelectMulti.

      Hope that’s what you were after.

  4. Is there a way to control the number of entries displayed in the list drop down? I would like to show up to twenty before requiring scrolling.

  5. I’ve got my lists down, but is there anyway to limit the number of times a selection is chosen? For example if my list contains 50 names but after that name has been chosen once, I don’t want it to be available in the drop down list for the rest of the column or sheet.

  6. Great post. I’m creating a list describing the subject matter of a variety of reports, using a drop-down list to indicate the subject. However, some reports are cross-cutting. Is there any way to select more than one option at a time using drop-downs, having the multiple results appear in the cell at the same time? (perhaps separated by a comma or a slash?)

    • admin

      Not with Data Validation, but you can use an ActiveX ListBoxand set its Multi-Select property to True (1 – frmMultiSelectMulti).

      To pull results in one cell I suppose you could use concatenated VLOOKUPS.

      Note that it sounds like it might be more trouble than its worth.

  7. I have like thousands rows that required me to use data validation list to use as data entry. Now i want to use Combo Box, as it has a feature that I can just type one letter to show all items with the behinning of letters selected
    My question is do I need to set the combo box one by one for those thousands rows? Is there any other way to do it

  8. I am just starting with data validation, and finding it very usefull..but how do I go about doign the following:

    I can setup a dropdown box with certain brands of equipment with no worries, but in the next coluum, i need to just show the various models from the brand I selected in the previous coluum.eg brand one has models a and b, but brand two has models c and d….how can i set this up, so that only options for c and d would show up because brand 2 was selected previously???

    does that make sense to you???

    Thanks.

  9. I like the data validation function, but two questions: How do I set the font size and color? How do I refer to it with a VBA event? I tried a sub like Private Sub dropdown1901_Lostfocus()and it didn’t seem to recognize the reference to the dropdown name. Thanks.

  10. I would like my dropdown menu to have checkboxes in order to select more than one entry. Then I will want to sort my table based on the entries. i.e. I am working with geographical zones. Some lines will require more than one geo zone entry.

  11. I want to create a combo box that a user can add to. I have a list of major manufacturers, but the team may have to add to the list. I would like it to update the choices in the combo box with the new manufacturers.

    • admin

      You can use Dynamic Data Validation. Goto Insert–>Name–>Define–>New. Add your list name and in the refers to box add:

      =OFFSET(‘Sheet1′!$A$2,0,0,COUNTA(‘Sheet1′!$A:$A)-1,1)

      Where sheet one column A holds the list. A2 implies that you have a header row for your list. When you set up your Data Validation, just refer to that list name. Anytime an entry is added to the list it will be added to your DV list as well.

  12. Ed

    I want to create a calendar with two drop downs on each line. One for a name and the other with the job assigned. Help!

    • admin

      You’re not giving much to go on. I suppose you could refer one Data Validation to an employee list and another to a pre-defined set of assignments?

      If you give more specifics I can give more back.

  13. How to create a sub menu from previously selected option from a list? For example; I have a list of states, then when I choose a specific state, I want to display a list of cities for that state only in a seperate menu (to show only cities of the state selected)

  14. Thanks for your comment… it me let me alot, I did the validation list but now i want my list typable as when i enter a alphabet and the all names under the same alphabet appear instead of scroll. I saw u reply on 30/01/2010 but still unclear on how to do it as i could not find any menu under the command of “combobox” . Iam using excel 2007. Kindly look into this. thanks.

    • admin

      Yes, you need an ActiveX ComboBox to have that functionality. In Excel 2007 goto the Developer tab–>Insert–>ActiveX Controls–>ComboBox. Once drawn on the sheet you can either hit the Properties icon on the Ribbon, or right-click the control. In the Properties window goto ListFillRange and add your range.

      If it’s on the same sheet you can refer to just the cell address: A1:A5

      If it’s on another sheet you need to add the sheet name: ‘Data Sheet’!A1:A5

      Or if you have a named range you can refer to that directly: MyListName

    • Abby, sorry for the delay.

      If you’re in Excel 2007+ go to the Developer tab. If you don’t have it visible, you need to go into FIle–>Options–>Customize the Ribbon and click the Developer button. Now when you go there you’ll see Insert. From there, select a Combo Box from the second set of controls. Then you use the Properties dialog (Properties button) to assign your range. When you’re done, click off Design Mode.

  15. Is validation available in windows Mobile?

  16. I would like my dropdown menu to have checkboxes in order to select more than one entry. Then I will want to sort my table based on the entries. i.e. I am working with geographical zones. Some lines will require more than one geo zone entry.

    • admin

      That’s only going to happen with a 3rd party control or if you build your own user form.

      But the good news is that you can use a ListBox and set its MultiSelect property to True. That way users can make multiple selections.

      You might also want to look at AutoFilter, which allows you to select multiple criteria to filter/display.

  17. I am using data validation with lists on another sheet. I just realized I need to change the wording of some of the selections. I wouldl ike to change on the list, then have all of the items already select in the data to reflect the new wording, without haveing to remake all of the selections from the dropdowns. Thanks.

  18. admin

    The easiest thing to do would be to create a VLOOKUP table with the old list and the new. Then use VLOOKUP to reference the old list, creating the appropriate matches. After that you can copy/paste special–>values the revised list over the original.

  19. I’m trying to set up a sign in sheet for staff members. I need to put the day and date at the top with each staff member’s name listed down the left side of the sheet. There will be one sign in sheet per weekday.

    How do I set up Excel to automatically change the date?

    Thank you

    • admin

      Sorry for taking so long to get back to you. If this is something you print out weekly, you can probably use =TODAY() for the first date, then add to it for each additional day with =TODAY()+1, or =Sheet1!A1+1 if you want a sheet reference.

      Noting that those will be dynamic dates, so if you want static dates you’ll need to use VBA.

  20. Hi,
    Loved the method 2. I’ve created the drop down and done all that is required for displaying an error message. However, i can still enter values that are not present in the list and the error message pop up does not appear as well.

  21. Also.. I am trying to automate this procedure as my list will keep changing. What would you suggest is the best way to do it? Is it better to create a macro and then run the macro every time I want to do it or will I have to write a vb script and source the list in it every time?

  22. Hi,
    The “Show error alert after invalid data is entered” is checked. Is this a bug or is there some other issue?

  23. Hi I’ve managed to create a project that automatically opens and excel file then at a pre defined time execute a macro. about half way through i get a message box that asks if i want to replace the contents of a cell. Is there a way i can automate a yes click on this box???

    • admin

      You can use:

      Application.DisplayAlerts = False

      Which will suppress those messages. Just make sure to set it back to True at the end of your code because the VBE won’t reset it.

      Sory for taking so long to respond, but I just got a new PC up and running after my old one died.

  24. MP

    Can you create multiple choice using a validation list?

    • admin

      What do you mean by multiple choice? Can you clarify? You can have multiple options in a validation list, but I need a better idea of what you’re trying to accomplish.

  25. CM

    Hi,
    I’m trying to create a dependent list, which will allow a price to be selected based on the object’s size. I’m wondering whether it is possible to name a list a number like “1’0 X 2’0″?

    • admin

      Sorry for taking so long getting back to you. The short answer is No. But you can just create a list with your callouts. E.G.

      1’0×4’0
      2’0×4’0
      3’0×4’0
      4’0×4’0

      And name it something like “Sizes”, then for your data validation Source just use =Sizes and your list of callouts will show up. You can then use a lookup table to reference the correct price. Note that if you’re pricing by UI you’ll also need to convert the callout size. I also have a method for doing that and showing an actual callout with the “oh” in superscript. I’ll be happy to send you an example if you want.

  26. Hi, In reference to the post on July 15, I have a list whose values keep on changing. The problem is that values selected in the dropdown (before the change) do not change automatically. How do I automate this procedure ?

    Thanks

    • admin

      The only way I can think of offhand would be to build a reference table to show what the old values are in relation to the new ones. E.G.

      Dog – Mastiff
      Cat – Calico

      If you have that it shouldn’t be too hard to use the Find method (VBA) to loop through the existing selections, compare them to your list and swap out the new value if a match is found. If you post back with some specifics (cell range for the currently selected validation items and the range for the swap list) I can probably put together an example.

  27. Maybe you should edit the webpage name title SmittyPro » Automating Data Entry with Data Validation Lists to more suited for your blog post you write. I loved the blog post all the same.

  28. [...] SmittyPro » Automating Data Entry with Data Validation Lists [...]

  29. Is it possible to create data validation where more than one option is selectable. For example, I have four different possibilities, AP, AR, Automation and P2P. Some cells may be only one type, where others may be all four.

  30. I have created a form with several Data Validation lists that are quite lengthy. Is there a way to get the lists to take me to whatever letter I am typing instead of having to scroll through the list and select an item?
    Or, is there a better way of doing this altogether?
    i.e. We have about 200 company names that we need to select from. I would like to start typing a name in the cell and have the corresponding names pull up from my list.

    • admin

      If you use an ActiveX ComboBox you’ll get that type of behavior. Just set the ListFillRange to match your data validation list’s range. Post back if you need more help setting it up.

  31. Paul

    Hi, I have created a data validation list, but I need to keep adding to this list as more stock items are added, how can i ensure the list is updated to take on the new entries in the data validation selection. So for instance I have names a range 12 to 20, but I now add an item in 21 how can this be reflected in the data validation. Many thanks Paul.

  32. Joe R Mikels

    Originally Posted By PoojaHi,
    Loved the method 2. I’ve created the drop down and done all that is required for displaying an error message. However, i can still enter values that are not present in the list and the error message pop up does not appear as well.

    I’ve kind of got the same issue except that on my months list, I get the error message pop-up if I enter a non-existent month. Even though I’ve got it capitalized in my months list, it still allows me to type the months in lower case. My goal is to force the used to select from the drop-down and not manually enter it. Any suggestions would be greatly appreciated.

    • Unfortunately, there’s no way to prevent a user from making an invalid entry, since validation can’t evaluate an entry until after it’s made. Then you get the error message.

      You could use a sheet activate event to pre-populate the dv cell with the current month if that would help. That way there’s already a selection made.

  33. Lisa-Marie Melvin

    Hi

    I was wondering if it is possible for a list to be displayed in colour for example:
    No – red
    Yes – green
    Na – orange

  34. Doug

    I am using data validation list that requires a whole number equal to or greater than 1. I want to copy the list to several rows, but don’t want to see the list data value until my cursor is on the list for each row.

  35. LJ

    Hi Smitty,

    Thanks for your fabulous post. We have a validation xls which is fab, however we have multiple clients that require a differing internal review panel for pieces of work… there are staff who need to update the form to reflect who the reveiw panel is (i.e. per customer there are a couple of panellees who differ from the standard group, but knowing who they are can be a nightmare. We have them listed in the validation, but we’d like them to do the form within an outlook template, so we as admins can just send the email directly to the review panel. Can the datavalidation be ‘exported’ or transferred into Outlook to work as per xls, or do I need macros etc?

    Comment Tags: data validation, outlook

    • I don’t know if you can export the validation itself to Outlook, but you can create custom forms in Outlook. You could export the list from Excel to Outlook and use a VBA combo box. But I’m just guessing on that as I haven’t worked behind the scenes in Outlook in years.

      Excel MVP Ron DeBruin is the real genius when it comes to programming between Excel and Access. http://www.rondebruin.nl/tips.htm

      And he’s a nice guy and should respond to questions.

      Smitty

  36. I want to change the footer automatically, by selecting from the drop down list.

    • You would need to use the BeforePrint event to read the footer. You can record applying Page Setup up with a custom Header/Footer, then modify the code to use the validation cell. E.G.

      With ActiveSheet.PageSetup
      .CenterHeader = Range("A1").Value
      .CenterFooter = Range("B1").Value
      End With

  37. I have validated a data in a cell, while printing the sheets, i want that cell data in Header / Footer. What can i do?

  38. I validated a data(35Nos), thru validation, i retrieved from various sheets. so 1 sheet contains all data. Now i want to separate the data in a sheet to another excel sheet i.e. 35 Sheets.

    It is possible in MSWord Using, Mail Merge, we can separate data and converted to 35 pages. Each page contains 1 data pertaining to particular items.

  39. Shelly

    I made a data validation list (say in cell A1) where the list source is on a different sheet. In B1, I want to automatically display the value of the cell in the next column over from the source of the data validation list. I know I will need to use the OFFSET function but I don’t know how to refer to the source address of a specific choice in the data validation list. My data validation list source is dependent on other items in my spreadsheet, so I would like cell B1 to populate automatically once a choice is made in the data validation list.

    Thanks for your help!

Leave a Comment - Here's your chance to speak.(eMail will not be published)

Tags:
Separate individual tags by commas

Before you post, please prove you are sentient.

What is 6 times 9?

Microsoft MVP

 

Join In

Tag Cloud

Great Links

Archives

SmittyPro.com

Welcome to SmittyPro.com, your one-stop for solutions concerning anything from beginner to extremely advanced Microsoft Excel issues and programming. Topics covered include Excel, Access, VBA, and (every now and then) some amazing observations or "Smitticisms" that might dumbfound you and send ripples of excitement or intrigue around the world.

Categories

Meta

RSS Top Stories from CNN

Vistor Counter for SmittyPro's Blog