Written by Allen Wyatt (last updated November 3, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
Carol asked if there is a way in Excel to create drop-down lists so that the second drop-down list is dependent on the selection made in the first drop-down list.
There are actually a number of different ways you can accomplish this task, ranging from simple formulas to complex macros. The method you choose depends, most directly, on the type of drop-down lists you want to create. There are actually three types of drop-down lists you can create in Excel:
Rather than discuss how to create dependent drop-lists based on each of these types of drop-down lists, I'll choose to examine the simplest method, which will suffice for most people. If you use the INDIRECT function along with data validation lists, it is quite easy to get the result you want:
Figure 1. The Data Validation dialog box.
=INDIRECT(A3)
That's it. Now people can only select from your major list if they are using one of the cells specified in step 6, and from the appropriate dependent lists if they choose one of the cells in step 11.
There are lots of different variations of this approach (using data validation). You can find more information on some of these approaches by visiting these Web pages:
http://www.ozgrid.com/download/ (download the MatchingLists.zip file) http://www.contextures.com/xlDataVal02.html
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2972) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Creating Dependent Drop-Lists.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Want to know what is happening in certain cells in your worksheet? Using the Watch Window is a great way to keep an eye ...
Discover MoreAs components of the Microsoft Office suite, one would expect Excel and Word to work together. One of the most common ...
Discover MoreWhen you no longer need a view, you can get rid of it by deleting it. Deleting unnecessary views is a good idea because ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments