Creating a Drop Down List with Dependant Values in Excel 2003/XP/2000/97.

Answer:

There are quite a few ways to create a Drop Down List in excel, but the easiest is like this:

1. Open a new Workbook.

2. On Sheet1 enter these headings into cells A1:L1

NBA, MLB, NFL, MFI, NBC, BNQ, BBF, BBC, TNT, MFI, MBA, LLB

Those are the values of the first drop down list.

Excel Drop Down List Entries

3. Now enter second drop down list values below these headings, down to row 18 for example, as it's shown on the picture.

Excel Drop Down List Value Entries

4. Now select A1:L1 then click in the Name Box (left of the formula bar) and type the name: List1 and press Enter.

Name the cells of the list

5. Now select A1:L18 (or the last row of the longest list) and go to Insert > Name > Create.

Insert > Name > Create

6. Ensure that only a "Top Row" is checked then click Ok.

"Top Row"

If you now go back to the Name Box you will see 12 named ranges.

Note that if you've had a Name with space in between Excel will use the Underscore for names that contain spaces (NBA_Team).

7. Now click onto Sheet2 and select cell A1, go to Data > Validation, choose the "List" option then type: =List1 in the source box.

Data Validation A1

Ensure "In-cell dropdown" is checked and click Ok.

8. Now select cell B2 and again go to Data>Validation, choose the "List" option then type:

=INDIRECT(SUBSTITUTE($A$1," ","_"))

in the source box. Ensure "In-cell dropdown" is checked and click Ok.

Note that formula points to the cell where your first Drop Down List is located.

Data Validation B1

9. Now select a list from cell A1 and you will get the appropriate list in cell A2.

This is it. Now this should all work. You can change the names, add or modify the Teams and even have as many lists as you wish.

Links to other Excel Tutorials we Recommend

MS Office Online Tutorials - Search Microsoft Office Online for more Excel tutorials and trainings.

Excel 2007 Online Tutorials - Microsoft Excel 2007 tutorial online by Denise Etheridge