Conditional Data Validation
Data Validation is great when you want to limit possible entries to a predefined list. Sometimes, however, you want that list to be different based on another cell. This posts shows you how to use Data Validation to condition one list based on the selection in another.
To do this, we need to set up some lists and name then using Insert>Name>Define. Here are some lists that I set up to illustrate.
The comments at the bottom of the lists show what I’ve named them. The first name “Pubs” is unimportant. But the other two names match the entries in Pubs exactly. This is important as we’ll soon see. I’ve set up DV in cell E1 as shown in the comment to that cell. In E1, the user has the choice of Books or Magazines. Cell F1 also contains DV, but the choices in that list will depend on what is in E1. Here’s what the F1 lists look like for different E1 values.
The DV in F1 is setup like this:
By naming the dependent ranges the same as the cells in Pubs, the INDIRECT function can be used to refer to them.
One cautionary note: If you change E1 after F1 has been selected, you’ll end up with a value in F1 that’s not in the appropriate list. No warning, no error, nothing. To alert users of this potential problem, consider adding conditional formatting to F1 to make it standout if the entry is not proper. Here’s one way you can do it.
Colin Bennett:
NOTE: Make sure a value is selected in the INDIRECT reference cell before creating your INDIRECT list. If you don’t do this Excel will display an error.
24 August 2004, 3:46 pmSarah:
Hi,
this solution for conditional formatting does not seem to work in Excel 2003.
Could you advise please?
Thanks,
Sarah
1 December 2004, 1:14 pmS Brijnath:
hi there,
is it possible to create a third level of contingent lists?
ie index of Time magazine?
regards
29 July 2005, 2:25 pmS Brijnath
Atul sharma:
Hi,
I can’t express my feelings. however i am very gald to get the right thing what i was looking for, from one week. best wishes for you and good luck.
Thanks & regards,
10 January 2006, 6:43 pmAtul sharma
Marty:
Hello,
What was the answer to the question: Is it possible to create a third level of contingent lists?
Or is it possible?
This question was posted on July 29th, 2005 by S Brijnath.
Thank you,
9 February 2006, 3:46 pmMarty
Joe:
Thank you very much! I was afraid that I was going to have to purchase a book that I kept getting referred to.
This is a very clever fix to what appears to be a common need.
8 May 2006, 2:01 pmGwynne:
I, too, am very interested in a third level of a data validation dependent list. Does anyone know the answer? I have not seen it addressed positively or negatively on any of my usual sites…
Thanks and good day, G
2 June 2006, 3:41 pmSanjib:
Hi,
I was really really impressed. I have been looking for something like this for a couple of days and this one is really amazing.
Keep up the good work
Many Thanks
Sanjib
3 July 2006, 9:30 amTushar Mehta:
For those who are interested in seeing how to implement this kind of scheme using a relational database structure (and supporting multiple levels) see
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html
For a Inter/Intra-net based solution, see
3 July 2006, 3:53 pmBuilding cascading queries
http://www.tmehta.com/krtest/index.asp
though this (the last) URL is almost certain to change at some point in the future.
Gary:
Loved it.
Thanks
19 July 2006, 1:03 amMark:
Is it possible to use conditional validation on a userform?
26 July 2006, 10:37 amGordon:
I had been using the following to achieve similar results:
=IF(COUNTIF(INDIRECT(G30),H30)=0,TRUE,FALSE)
Which works fine when testing in a sheet where two conditionals are entered (i.e. the third selection is dependant on the second, which in turn is dependant on the first). But when I enter this formatting in my ‘live’ sheet which was the trigger for me trying to find something to flag errors in validation, it always throws up an error.
I was interested to try this ISERROR solution aswell but it does exactly the same; the formatting works fine (even when used in 1000 rows across two columns) and saves fine, but on reopening Excel (2003 here) crashes and I lose everything.
I’m resigned to the fact there might be something else in the sheet that is causing it, but whatever it might be is beyond my ken.
Anyone have similar problems using this conditional formula formatting?
Also, in theory, is either method preferable in terms of speed/complication?
8 August 2006, 6:25 amMargaret:
First, let me say “WOW”!! Awesome website. I start checking out other categories and almost forgot why I got here in the first place.
2 January 2007, 8:13 pmI am working with Excel 2003 (just recently upgraded from 97)
I am creating an excel workbook that will contain 8-10 User worksheets each the same except for different depts. There will be multiple uses of each of these worksheets. I have tried to keep the worksheet simiple for the user by have most of the entries come from drop-down lists. I designed a separate worksheet (within this workbook) which holds all the lists being referenced in the drop-downs. What I would like to do is, is on the List Worksheet, link certain cells within a list to another worksheet (still in the same workbook) to display a series of 6 to 8 cells of Detailed Information. I am able to create this link between the cell within the List worksheet and the Detail Info worksheet, however on the Users worksheet, when I test the cell which holds the drop-down list with the values link on the List worksheet, I only have plain text - no link?? Am I going about this the wrong way? Any Suggestions would be greatly appreciated. Please note when the workbook is complete, the List and Detailed Info worksheets will be locked and “well hidden”.
Thank you
Tom:
Very nice tutorial. I was getting very frustrated trying to do this. Thanks for the help!
2 April 2007, 12:36 amSam:
BEWARE!!
I get the same problem as Gordon (12) in Excel 2002; after putting the ISERROR in, saving, exitting and reopening, excel crashes.
Note: I just tried it on a simpler spreadsheet - no problem. hmm.
His workaround has an issue: it only applies formatting if F1’s value is a defined name.
Anyone know how to do this?
13 April 2007, 9:50 amKenri:
Does anyone have an answer to Margaret’s comment/question? I’ve had similar questions about displaying detailed information for single list entries and have begun to consider a few possible solutions; such as:
Using a Concatenate function that takes on an array to display at least some of the text.
Having a paste picture link situation in an Shape layer or in the comment.
Having the data from the two worksheets in separate external database files for filtered reference.
But none of these are simple or particularly more efficient than going to something like Access for a relational database.
Am I on the right track here?
19 April 2007, 9:07 pmAamer:
Hi,
I have a similar problem. I need to get data from two lists, one dependent on the other. I have followed the above procedure, it works, but the problem is regarding addition of new items in the first list(the independent one). Is there a way that when a new item is added a similar named range is also added to the worksheet, so that, the new added item also appears in the independent list.
Will appreciate any and all help in this regard
22 May 2007, 6:32 amArun:
Hi,
I’m trying the same in excel 2003, I always get an error “the source currently evaluates to an error.do you wish to continue?” I’m following the same steps as mentioned above…
4 July 2007, 5:34 amPlease help.
ganeshps:
I tried this in XP as told here. It is not working. Kindly please help.
10 July 2007, 1:09 amNiki:
For those who are interested, the third level works exactly the same as the second. Setup the validations lists for each of the values on the second level and remember to name the lists properly (i.e. the name of the choice from the second level).
Good luck.
5 September 2007, 9:23 amAnju:
Hi,
I am trying to have third list but which is based on first list. That is i want two lists depend on the first item.
let’s say i have list of failure components in the first list, depends on that failure component i want its loaction(like which part of the component) and observation(Reason).
Will appreciate all your help in this regard.
Thanks
26 October 2007, 11:08 amJason Andrade:
My question is exactly like Anju’s directly above anyone have any insight?
27 November 2007, 2:50 pmDick Kusleika:
It’s sounds like you guys want VLOOKUP rather than lists based on lists. Send me an email with a specific question, if you like.
27 November 2007, 10:05 pmRAHUL KUMAR:
this information is exectly same i was looking for it solved all my problem in few minutes.
28 December 2007, 4:14 amgood work and thanks
Amy Rule:
This DOES work with Excel 2003. I suspect the problem some people have been having is the one I had. My group headers were multi words. In other words, instead of something simple like “Pubs” and “Books” and “Magazines” I had things like ‘Database Group” and “Admin & General” as my group headers. Range names cannot include special characters or spaces. And for this to work, the range names HAVE to be exactly the same as the column headers. So I changed all my column headers to eliminate special characters and use _ for spaces and everything worked just fine in Excel 2003.
28 January 2008, 9:17 pmMark:
This conditional DV is great, except you all should know that it only works with simple formulas. I tried to use the =INDIRECT(E1) substructure and it didn’t work for me.
I realized that this is because my primary level DV lists were too complicated for the sub-tier DV list to handle. My primary structure was as follows:
=OFFSET($E$1,0,0,COUNTIF($E$2:$E$100,”",0))
This formula varies the length of the list dynamically depending on how many entries you have. This way, if a user adds to or subtracts from the list, the DV adjusts to the new area. Unfortunatly, the =INDIRECT function does not play nice with this DV structure.
If anyone has any ideas on how to marry these two together, I’m all ears!
Mark
16 February 2008, 5:23 amMark:
I also received the “Evaluates to an Error” message. My problem was solved when I simplified the formulas for all of the lists. Instead of using the OFFSET formula (which I discussed in my last post), I simply used the =$E$2:$E$14 to identify the list items. After this, I stopped receiving the error. If you are having a problem with this error, make sure that all of your DV lists use very basic formulas. This may solve your problem as it did for me.
Mark
16 February 2008, 5:34 amsandra:
This is great and solved my initial problem . I have a slightly different validation issue. I’d like to have the custom validation alllow the user to enter any number, or the text “researched, not avail”. I can’t get the formula right. What function do you recommend?
20 February 2008, 9:43 amTrevor Clark:
An absolute life saver ! - Saved me many many hours of work - set up took about an hour (a very complex data set for the lists) - YES it most definetely works with three levels and in fact I cant see why it wouldn’t work with more (but I would hate to build it)
The rules for the third list must follow the same rules as for the second - I found naming tricky but a simple little “_” instead of a space solved the problem, I note one entry where they asked about adding more values to the ranges (since I’m pretty slow I found I had to do this)- for me the answer was pretty direct - when I named the range on the FIRST list, I added a blank line at the start of the list(to make sure the drop down validation list behaves nicely) BUT I also left some Blank spaces at the end but included in the named range) - then when I needed to add another dependent set I just added an entry to the Primary List and then Inserted a column in the second set of List data, followed the rules on naming the range, and did it again for the third dependent list. Viola !
One other question I read was having two different lists dependent on the First - I cant see how that would work, because the linkage between the lists is the exclusive delaration of range name that matches the primary list contents ie Books or Magazines from the example
1 May 2008, 5:52 pmDick Kusleika:
To have two lists based on one, just append something to the end of the range names. Make a list named Books1 and one named Books2. Then in your DV
3 May 2008, 8:39 pm=INDIRECT(E1&1)=INDIRECT(E1&2)Steve:
Excellent tip. Really helped me sort out an easy to use spreadsheet for the not so computer literate! Thanks.
7 August 2008, 4:28 amShane:
For anyone that is having excel crashing issues with using “iserror” in the data validation section, try using “isna”. This fixed the issue for me and now my (30k+ rows) sheet is not crash looping.
8 August 2008, 12:11 pmJon:
Another way to deal with an invalid F1 when H1 has changed is to put a worksheet_change event procedure on H1 to reset F1 to blank when it has changed.
1 September 2008, 10:59 pmRon:
Excellent Site.
4 September 2008, 12:45 amI’ve discovered an alternative to the above that may interest some. Create lists on one sheet and display them on another.
Using the example above you can have many selections from many lists, it is very easy to increase the number of lists and number of selections:
Create your lists on sheet2
Name the first list + some blank cells below (for future completion) “Publications” (say A1:A10)
Name heading of each list e.g. “Books” (B1), “Magazines” (C1) + any others you require.
Name the list below each heading (extend the range to include blanks for completion at a later date) e.g. “Bookslist” (say B2:B20) ,”Magazineslist” (say C2:C20) etc.
Now on sheet1 say cell A1 allow ‘list’ validation criteria and enter a source
=Publications.
In cell B1 and as many other cells as you wish allow ‘list’ validation criteria and enter a source
=OFFSET(INDIRECT(SUBSTITUTE($A1,” “,”")),1,0,COUNTA(INDIRECT(SUBSTITUTE($A1,” “,”")&”list”)),1)
Change A1 to reference the cell where you place your publications list.