Apply Data Validation
The next step is to apply Data Validation. Enter headings into the first row, if you choose. Then select the cell or cells in which you want a drop down list showing the first list you created. In this example, cell A1 contains the heading “Arizona Counties." Cells A2 through A10 contain the Data Validation drop down list “Counties."
Apply Data Validation to the dependent cells. Select the cell or cell you want to contain the drop down list that appears depending on which option the user selects in the original list. For our example, cell B1 contains the heading “Arizona Cities." Cells B2 through B10 are selected.
If you are using Excel 2003 or earlier, go to the Data menu and click on Validation. If you are using Excel 2007, go to the Data tab, select Data Validation and choose Data Validation from the drop down menu. The Data Validation dialog will appear. Choose List in the Allow box. Type =INDIRECT in the Source box. Directly after the INDIRECT function, enter the first cell with data in your original list. In the example spreadsheet, we entered =INDIRECT(A2), because A2 is the first cell with data that affects the dependent list.
Click OK. Excel will show an error message, asking if you wish to continue. Click Yes.