Automating Data Validation Lists in Excel

exceltips.jpg

Spreadsheets and graphs on a desk
xfgiro/istock
34

Working in an Excel spreadsheet can be somewhat like the Wild West – unless other provisions are made, users can enter any value in any cell. One way you can restrict users to a predefined set of values is by way of Excel's Data Validation feature. I'll explain how you can create in-cell drop-down lists, along with how to "future proof" the Data Validation list so that the feature won't require maintenance if you add additional items in the future. This technique also resolves an annoying problem in Excel 2007 where data validation lists cannot be placed on other worksheets.​

Before we look at Data Validation, let's first establish our list. Let's say we want the user to choose a type of fruit. On the second worksheet of a blank workbook, create a list, such as shown in Figure 1. Once you do so, make the list into a Table in Excel 2007 and later, or a List in Excel 2003 and earlier. After you click on any cell within your list:

 

  • Excel 2007 and later – Choose Insert and then Table. Make sure that My Table Has Headers is selected and then click OK.
  • Excel:Mac 2011 – On the Tables tab of the ribbon, click the arrow next to the New command and then choose Insert Table with Headers.
  • Excel 2003 and earlier – Choose Data, List, and then Create List.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

avatar
By kiz
Jun 26th 2015 01:11

Excelant :D Just what I was looking for. Thanks man.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

Thank you for the feedback! I'm glad you found my article helpful.

Thanks (0)
avatar
By shanmuga nathan
Jun 26th 2015 01:12

Hi David,

First of all, Thank for your article, really it helps lot.
I have one question in Drop down list in Data validation.. I have around 1500 in the list with names started "A to Z", I want to select one of the names with started "K". here I could not go directly "K", i will have to click n number of page downs.. hope you understand what i am questioning you.. rquest you to help me o this.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

You're going to have to be a bit inventive here and insert 1500 blank rows beneath your Data Validation cell that you populate with your list. You'll then hide those rows. Having the list entries adjacent to the cell you'll type in enables the Excel feature that shows you related items you've typed in the adjacent area. Data Validation then prevents the user from making an invalid entry.

The other solution is to use a Combobox form control, but you can't access those from the keyboard, you have to click in the control with your mouse but then you can type the first letter and navigate the list.

Thanks (0)
avatar
By shanmuga nathan
Jun 26th 2015 01:12

I am sorry to say that I unable to understand "Having the list entries adjacent to the cell you'll type in enables the Excel feature that shows you related items you've typed in the adjacent area." this line.. can you please give me some more brief on this..

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

Go to a blank spreadsheet and type Red in cell A2, White in cell A3, and Blue in cell A4. Type the letter B in cell A1 and you'll see Excel offer to fill the cell with Blue. Add more items to rows 4 through 10 such as Rad, Brew, Whale, etc. and as you type two or more characters in cell A1 you'll see the prefill options change. Then apply this concept to your spreadsheet, but with placing all 1500 list items in hidden rows immediately adjacent to your data validation cell. It's not elegant, but works within the constraints of Data Validation.

Thanks (0)
avatar
By shanmuga nathan
Jun 26th 2015 01:12

Hi David,

I am very much clear.. It was wonderful idea.. how ever my case is quite different.. I will give some samples in the list here

BIRLA SUN LIFE INSURANCEBEAWAR
BIRLA SUN LIFE INSURANCEBIKANER
BIRLA SUN LIFE INSURANCEFATEHPUR
BIRLA SUN LIFE INSURANCEHANUMANGARH
BIRLA SUN LIFE INSURANCEJAIPUR
BIRLA SUN LIFE INSURANCEJAIPUR 2
BIRLA SUN LIFE INSURANCEJALORE
BIRLA SUN LIFE INSURANCEJHUNJHUNU
BIRLA SUN LIFE INSURANCEJODHPUR
BIRLA SUN LIFE INSURANCEKISHANGARH
BIRLA SUN LIFE INSURANCEKOTA
BIRLA SUN LIFE INSURANCEPALI
BIRLA SUN LIFE INSURANCERAJSAMAND
BIRLA SUN LIFE INSURANCESIKAR
BIRLA SUN LIFE INSURANCEUDAIPUR
BIRLA SUN LIFE INSURANSAWAI MADHOPUR
BIRLA SUN LIFE INSURANSRI GANGANAGAR

I am really sorry for disturbing you again and again.. kindly help me.. o

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

We have a new forum section that is best suited to situations such as this where you need more assistance than I can provide in this context. Post your query here http://www.accountingweb.com/f... in the Technology section and perhaps another forum user will have the free time to assist you. Good luck with your project!

Thanks (0)
avatar
By shanmuga nathan
Jun 26th 2015 01:12

Hi David,

Thanks for your guiding on this..I will do it

Thanks (0)
avatar
By Dilip
Jun 26th 2015 01:11

Excellant thanks

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

My pleasure!

Thanks (0)
avatar
By joão
Jun 26th 2015 01:11

Great. Helped a lot. Clever.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

Thanks for the feedback! I'm glad to be of assistance.

Thanks (0)
avatar
By CanIHasDropdowns
Jun 26th 2015 01:11

David
I'm building spreadsheets from a VBA macro in another app. I would like to build a sheet with my list values and hide the page, but I can't seem to add the drop-down menus on the user page. I can record a macro that creates the list and drop-down menu, but I can't seem to translate that to the original VBA macro I'm building.

This code will create a new sheet, build a list and name it.
(Note only the last five values are included in the list.)

Excel_App.ActiveWorkbook.Worksheets.Add
Set HiddenValuesSheet = Excel_App.ActiveWorkbook.Worksheets(1)
With HiddenValuesSheet
.Name = "menus"
.Visible = False
.Cells(1, 1).Value = "Status"
.Cells(2, 1).Value = "Completed"
.Cells(3, 1).Value = "Draft"
.Cells(4, 1).Value = "Signing"
.Cells(5, 1).Value = "Corrected"
.Cells(6, 1).Value = "Electronically Filed"
.Names.Add Name:="Status", RefersToR1C1:="=menus!R2C1:R6C1"
End With

Now I create a new page, but attempting to make a cell display a drop-down menu fails with "Run-time error '438': Object doesn't support this property or method". Obviously I'm doing something wrong :/

With DataSheet
.Name = "All Data"
.Cells(2, 2).Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Status"
End With

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

You didn't say which version of Excel you're using, but in Excel 2007 and earlier your lists can't reside on another sheet. Also, your line of code .Name="All Data" is likely causing an error because range names cannot have spaces in them. I'd consider eliminating the hidden list and embedding it into your Data Validation code. Also notice that you didn't call Data Validation itself, so the line that starts with .Cells(2,2).Add is invalid, and should be .Cells(2,2).Validation.Add:

Thanks (0)
avatar
By CanIHasDropdowns
Jun 26th 2015 01:11

David
Thanks for the quick reply! First, I'm using "Microsoft Office Professional Plus 2010", but thanks for that, I didn't realize how new the 'lists on separate tabs' feature was. The name was for the sheet, not the list, but I'll be sure to keep spaces out of the list names if I ever use them again ;)
Thank you for showing me how to build a validation list on the fly. I had mistakenly assumed that a drop-down menu was only entered as a named list in a separate sheet. Now, instead of building an entire page and hiding it, I'm adding the drop-downs to the end of each row with a single line:

With DataSheet
...
DataSheetY = DataSheetY + 1
.Cells(DataSheetX, DataSheetY).Delete
.Cells(DataSheetX, DataSheetY).Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "Completed,Draft,Signing,Corrected,Filed"
...
End With

Thank you so much!

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

You're welcome! It was a fun question, and I'm glad I could help.

Thanks (0)
avatar
By Welshgasman
Jun 26th 2015 01:11

I have used ranges in other sheets as validation on another sheet and as lists, and they work fine. However they do not work on 2007, which is why your article is a lifesaver. :-)

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

I'm so glad you found my article helpful. As you found Data Validation lists must reside on the same worksheet in Excel 2007 and earlier. The Table feature makes it a set-and-forget approach in Excel 2007 and later.

Thanks (0)
avatar
By Welshgasman
Jun 26th 2015 01:11

Just like to say 'Thank you' for this article. I was trying to use ranges and was planning on using dynamic ranges as the validation criteria, but excel 2007 in work would not allow it. Absolute cell references were find as the validation was on another sheet.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

I appreciate your feedback. Dynamic ranges are a viable solution as well, but it's hard for the uninitiated to wrap their brain around.

Thanks (0)
avatar
By Jacky Choo
Jun 26th 2015 01:11

I have a list of categories which some of them contains white spaces, for example, Green Fruit - Apple, Grapes, and so on
and some of the category start with number, I can't use them as the Name because the name distriction, need serious help on this~

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

You'll have to be inventive and use underscores "_" instead of spaces in your range names. Preface the ones that being with numbers with a \. You can then use the SUBSTITUTE function to replace the _ and \ characters as needed. You might also have to use the INDIRECT function as well. A great message board to post your question to is www.mrexcel.com if the aforementioned doesn't give you enough to work with.

Thanks (0)
avatar
By JP
Jun 26th 2015 01:11

Hi. Is there a way (excel formula or VBA) wherein I could choose from a drop-down list in Cell 1 and then with my choice, a unique set of items (in drop-down) on Cell 2 will appear?
Ex.
Cell 1 - White, red
If i choose white, cell 2 should have a drop-down with 1,2 and 3
If I choose red, cell 2 should have a drop-down with 4,5,and 6.

Thank you!

Thanks (0)
avatar
By Carlos Ernesto
Jun 26th 2015 01:11

Yes you can,
Put a Name to your lists >Formulas - Defined Names - Define Name<. On Cell 1 in the Data validation Source enter your main list name, then on Cell 2 in the data validation Source enter "=INDIRECT(cell1)" . This will show the drop down for the list you have chosen on Cell1.
ie
List "Main":
- Red
- White
List "Red":
- Apple
- Strawberry
List "White":
- Eggs
- Coconut
On Cell A1 data validation source =Main
On Cell A2 data validation source =INDIRECT(A1)

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

I somehow missed the notification on this question. Thanks for sharing the solution, Carlos!

Thanks (0)
avatar
By AU
Jun 26th 2015 01:12

Hi, Is there a way where in the user can only use the validation on the cell and is not copy paste some other value.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

Unless you go really deep and do some programming in Excel to disable the copy and paste commands every where you can't block someone from pasting over your data validation. But, if you unprotect your data validation cells, and then protect the worksheet, you'll at least force them into having to go into another worksheet to put the invalid value in. Further, when you get the spreadsheet back you can use the Circle Invalid Data feature on the Data Validation submenu to immediately identify any invalid values. Generally protecting the worksheet will keep most users in line with regard to Data Validation.

Thanks (0)
avatar
Jul 27th 2015 15:57

It requires some non-trivial VBA, but you could add a Worksheet_Change event handler to the sheet to validate the data using the Target cell and the named range of the validation table. This gives you a lot of options on how to handle invalid data - a pop-up error message (msgbox) or moving the cursor back to the invalid cell are two.

For situations where a simple warning will suffice, I've used conditional formatting to automatically highlight a cell with an invalid or missing value. Here's the formula:

=ISNA(VLOOKUP(J7,DataTable,2,FALSE))

Tom the Toolman

Thanks (0)
avatar
Jul 27th 2015 15:38

I was puzzled at first about the assertion that for Excel 2007 and earlier, "data validation lists cannot be placed on other worksheets". I routinely do this in both 2003 and 2007 and it seems to work fine. I simply use a defined name for the range of cells that covers the data for the validation list.

It turns out that the issue is due to the "table" or "list" feature in Excel. As I didn't know about this feature, I had never used it. (BTW thanks for that into - I love learning new stuff). I simply used a named range for my validation data.

Of course, when I want to insert items, I must be sure to insert *within* the list so that the named range is updated properly. Often, I then have to copy/paste or sort the data to put it where it needs to be in the list. But I inadvertently avoided the "different sheet" issue this way.

So I thought I'd mention this for users who really want to put the data validation list on another sheet. Just use a global named range for the values and it'll work fine. Just be sure to insert new entries between existing ones so that the defined name gets updated as needed.

Tom the Toolman

Thanks (0)
avatar
Sep 8th 2015 11:14

The Table feature makes it a set-and-forget approach in Excel 2007 and later

Thanks (0)
avatar
By panda1
Sep 8th 2015 11:19

Register multiple betting accounts

With odds on each of your selections multiplied with your stake to determine the potential winnings, even the slightest difference between prices can seriously increase or decrease your returns. It is, therefore, crucial to open betting accounts at multiple bookmakers, since this strategy will ensure your bets are placed at the best available odds week in week out. And considering that you will mostly be backing odds on favourites, doing some research on which bookies consistently offer the best prices on top sides could pay off big time.

Thanks (0)
avatar
Sep 30th 2015 16:24

Thanks for the insight. I know Data Validation is a "restriction", but I want to be able to add to (and alphabetically sort) my "fruit list" (table) from the cell A2 on my "first workbook". Is that possible? Perhaps this is another function?

Thanks (0)
avatar
Sep 22nd 2016 16:45

Nice one with clear explanation, I just got want i wanted. Thanks

Thanks (0)