1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Creating a Drop Down List in Excel Part 2

Using Data on a Different Worksheet for the Drop Down List

By

Create a Drop Down list in Excel

Create a Drop Down list in Excel

© Ted French

Related Tutorial: Step by Step Tutorial with Screen Shots - Creating a Drop Down List in Excel Pt. 2

A drop down list allows you to enter data into an Excel spreadsheet from a preset list of entries.

Part 1 covers the steps to creating a drop down list with data on the same sheet as the drop down list.

This tutorial covers creating a drop down list on a different worksheet.

Example: Creating a drop down list with data on a different worksheet

  1. Enter the following data into the correct cells on Sheet 1 or a worksheet:

    E1 - The Cookie Shop
    D2 - Cookie Type:

  2. Click on the Sheet tab for Sheet 2.

  3. Enter the following data into the correct cells on Sheet 2 or a worksheet:

    A1 - Gingerbread
    A2 - Lemon
    A3 - Oatmeal Raisin
    A4 - Chocolate Chip

  4. Drag select cells A1 - A4 on Sheet 2.

  5. Type " cookies " (no quotes) in the Name Box and press the ENTER key on the keyboard.

  6. Click on the Sheet tab for Sheet 1.

  7. Click on cell E2 - the location where the results will be displayed.

  8. Click on the Data tab.

  9. Click on the Data Validation option from the ribbon to open the menu.

  10. Click on the Data Validation in the menu to bring up the dialog box.

  11. Click on Settings tab in the dialog box.

  12. From the Allow menu choose List.

  13. Type " =cookies " (no quotes) on the Source line in the dialog box.

  14. Click OK in the dialog box.

  15. A down arrow should appear next to cell E2.

  16. When you click on the arrow the drop down list should open to display the four cookie names.

©2014 About.com. All rights reserved.