Search
« Excel - Special Names in Dynamic Lists | Main | Reclaim Main Memory on a Windows Mobile Device »
Sunday
Mar152009

Excel - Dynamic Drop Down Lists with Full Validation

I was recently asked to look at a problem a colleague was having with an Excel spreadsheet where they wanted to use a drop down list in the cells of one column and then a drop down list in the next column that was dependent upon the selection in the previous column. This is something I'd come across before and a little browsing of the help file refreshed my memory and I gave them a solution based upon data validation and the use of the INDIRECT function.

Here's an example:

Define the drop down lists Define the drop down listsFirst of all I've created a worksheet with some lists that will be used to drive my data validation lists. The three lists are "Type", "Fruit" and "Vegetable". I've not exactly been clever with this but I have applied a couple of things to make this easier.

The first is that I have named the cells in each column relating to the list. Providing a name makes it easy to reference particularly when I'm holding the lists on a different worksheet to where I'm going to be doing the data validation.

The second is the names I have chosen. If we take type as the 'primary' list from which the other two 'secondary' lists are being selected I've named the secondary lists precisely as the items are displayed in the primary list. I've put the name of each list selection in the top cell of each column to make it even more explicit, but this means I don't have to do any cross referencing or special look-ups later.

Next on my main worksheet I set up a couple of data entry columns. The first is for food and the second for a more detailed selection of the type of food. To set the first column to use an appropriately validated drop down list I select Data Validation from the Data menu (in Excel 2007 at least) and then select to use a list to validate and point to the named range "Type" as the source of the list using the INDIRECT function.

Data Validation for primary list Data Validation for primary list

Primary list drop down Primary list drop downMy selection is limited to the values in the named range of "Type", and should I try to enter something else into that cell I get an error message - just the default one in this case though it can be customised on another data validation tab.

Data entry error shows an error message Data entry error shows an error messageI now set-up the data validation for the "More detail" column in the same way as the "Food" column, but this time instead of using =INDIRECT("Type") as my list data source for data validation I instead use =INDIRECT(B2) for cell C2, and then replicate this down to the cells below (C3 references B3, etc.)

Secondary list drop down driven by primary list Secondary list drop down driven by primary listHere the drop down lists will now vary in accordance with the food that has been chosen in the previous column. Errors for the lists also seem to be intact as if we select the "Food" entry as being 'Fruit' and then try to type in a choice that is not in the list (in this case 'Radish' which isn't in any of the lists, but it could actually be anything other than 'Apple' or 'Pear' in this example) we get a familiar looking error message.

A data entry error is identified A data entry error is identifiedAt this point it looks like we have a simple and effective solution. But this is where I began to be a bit more thorough in my testing than I think many others have been. Specifically what happens when we don't have a food entry?

Problem - No food entry means no validation on more detail entry Problem - No food entry means no validation on more detail entryClicking on the drop down doesn't display any list entries for selection - because it is just an empty list. Typing an entry in however is accepted as there is nothing being validated against - as shown in cell C4. This is a bit of a problem ... but you've probably figured out buy now that I have a solution.

My original solution attempt was to change the source by checking if the food type had been entered and if it had then returning a blank list, otherwise the appropriate secondary list.

Taking cell C4 as my working example...

=INDIRECT(B4)

... was changed to ...

=IF(B4="","",INDIRECT(B4))

Unfortunately this yielded the same result in terms of the behaviour observed. I also tried replacing the conditional parameter of B4="" to ISBLANK(B4), but again this made no difference.

Next I tried replacing the list to use when B4 is blank with another list using the following data validation list source statement =IF(B4="","BLANKLIST",INDIRECT(B4)). The list BLANKLIST did not exist but again the same result. Again I tried using ISBLANK(B4), and again no difference.

I created the BLANKLIST named range with one entry of "N/A", but the only difference this made was that when the food entry was blank the more detail list now had one option of "N/A", but still anything could be types into the cell without a warning message.

As well as having the IF as the outer most function I also tried the various combinations with INDIRECT as the outermost function...

=IF(B4="","",INDIRECT(B4))

... became ...

=INDIRECT(IF(B4="","",B4))

... and so on for each of the options described above (which was quite a few!) but still nothing worked. Overall exceedingly odd behaviour when it is compared to what is happening in the cells above if something is entered that is not in the data validation list. This may be a bit of a bug in Excel's data validation processing.

In the end I split out my conditional processing from my data validation processing. This could be put onto a separate hidden worksheet or into a hidden column. For this example I've just placed it next to the more detail column.

Cell D4 contains the following function:

=IF(B4="","",B4)

Cell C4 then contains the following for its data validation list source:

=INDIRECT(D4)

A welcome error message A welcome error messageThe data validation finally works as expected enforcing the more detail entry to be from the appropriate secondary list unless the food entry is blank in which case no entry is allowed. Not the prettiest solution, but one which allows your dynamic drop down lists to maintain the enforcement of data validation where the primary data selection has not been made.

Edit:

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (6)

[...] IT The micro-blog for Reboot IT « Excel - Dynamic Drop Down Lists with Full Validation Excel - Special Names in Dynamic Lists 15/03/2009 I posted earlier about a better way for [...]

Great work, this really helped me out thanks!

June 1, 2009 | Unregistered CommenterAlex

Can you please email me the Sample Excel file. I'd like to use it. Thanks.

August 16, 2009 | Unregistered CommenterAlex

Rather than mail it out I figure it might be better to share it with everyone. I've added a link at the bottom of the post to an example spreadsheet. Click on the cells in the third column to see the difference in the drop down lists and change the lists in the first column.

Hope people find this helpful. Please let me know if you do by leaving a comment or even just rating this post by clicking on the stars at the bottom of the post.

August 19, 2009 | Unregistered Commenterthermalspindle

Cool site, love the info.

September 2, 2009 | Unregistered CommenterBill Bartmann

[...] 1. Excel – Dynamic Drop Down Lists with Full Validation [...]

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>