By Bill Jelen

Bill Jelen is the author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.

Learn how to use the INDIRECT function and data validation to set up two dropdown lists, where the choices in the second list change based on what is selected in the first list.

This article is an excerpt from Learn Excel 97-2007 from MrExcel by Bill Jelen. Buy the print edition or download a free e-book edition from MrExcel.com.

Other articles by this author

Excel 2007

• Sort all red cells to the top of a report
• How to fit a multiline heading into one cell
• Change the width of all columns with one command
• How to make a wide report fit to one page wide by many pages tall
• For each cell in column A, have three rows in column B
• Customize anything on a chart by right-clicking
• Display a chart axis in trillions
• Raise a number to a fraction to find the square or third root
• Total minutes that exceed an hour
• Calculate a running percentage of total
• Calculate a trendline forecast
• Rank scores in Excel 2007
• How to do 40 different what-if analyses quickly
• Back into an answer using Goal Seek

Excel 2003

• Use color banding to locate the active cell
• Three ways to convert numbers to text
• Five ways to subtotal values in repeating data
• Conditional formatting: Considering the fourth option
• Locate duplicates by using conditional formatting
• Change cell background color using VBA