How to Create a Drop Down List From Another Sheet in Excel (2 Methods)

final result

When you are working with a large database and you need to pick a specific item from a list, a drop-down list can help you in this situation. Using a drop-down list you can select any specific data in seconds. You can make your drop-down list from different worksheets too. Today, in this article we will discuss how to create a drop-down list from another sheet in Excel.

Quick View

Here is a quick view of our task

quick view

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Creating Drop Down List From Another Sheet (2 Ways)

Creating a drop-down list from different sheets is easy and can be done in two ways. Let’s discuss this

1. Drop Down List from A Single Worksheet

Step-1:

In the following situation, we are given a data range of the “Name” of some students and their “Subject” and “Department”. Name this worksheet “Source Data”. We need to make a drop-down list from these data in a different worksheet.

creating table

Step-2:

Create a new table in a different worksheet containing columns “Name”, “Subject”, and “Department” where we will create the drop-down list.

creating table

Step-3:

Now first we will make a drop-down list of the names. Select cell “B4”, go to “Data” and click on “Data Validation”Data validation

 

C4→Data→Data Validation

Procedure

Step-4:

A new window popped that shows how we want to have our drop-down list. Now click on this icon icon2 and select “List”.

procedure

Make sure “Ignore Blank” and “In-cell Dropdown” are checked. Now click on this icon icon1 to select your drop-down list data from a different sheet.

procedure

Step-5:

Again a new window appeared where we have to insert our drop-down data. To do this, click on the “Source Data” go to this different sheet.

differnet worksheet

Step-6:

We are now on our “Source Data” sheet. Select the data from the column “Name” and click on this icon icon3 to confirm the selection.

selecting data

Step-6:

Now that our drop-down data is ready, click “OK” to confirm.

selecting data

And our drop-down list is created. Click on this icon icon2  to show the list.

selecting data

Step-7:

Now do the same steps for the rest of the two lists. The “Drop-Down” list for the “Subject” column is this

getting result

And for the “Department” column the drop-down list is,

getting result

So, the final result is,

final result

2. Drop Down List from Multiple Worksheets

We can also make a drop-down list from multiple worksheets. This procedure is discussed below,

Step-1:                       

Let’s consider that, we are given some data tables containing the “Name”, “Subject” and “Department” of some students in different sheets. We have to make drop-down lists in a new worksheet from those different sheets.

For the “Name” data, the “Data 1” worksheet is created.

creating table

For the “Subject” data,

creating table

And for the “Department” column, another different worksheet is made.

creating table

Step-2:

Make a new table in a different worksheet where the drop-down lists of these data will be created.

creating table

Step-3:

We will make a drop-down list for the “Name” column first. Select the cell range, go to “Data” and click on “Data Validation” Data validation

Data→ Data Validation

creating table

Step-4:

A new window appeared. Click on this icon icon2 to open a chart where different styles of the drop-down lists are given. Select “List”.

procedure

Make sure to check “Ignore Blank” and “In-Cell Dropdown”. Click on this icon icon1 to continue.

procedure

Step-5:

Another new window popped out where we have to insert the data for our drop-down list. Go to the “Data 1” sheet to input data.

procedure

Step-6:

Now we are on a different sheet where the data are given. Select the data and click on this icon icon3 to continue.

selecting data

In the new appeared window the final selection is given. Click “OK” to proceed.

selecting data

And we have got our drop-down list for the “Name”.

getting result

Step-7:

Now do the same for the “Subject” and “Department” columns.

The final selection for the “Subject” column is,

getting result

The drop-down list for the “Subject” column,

getting result

And after collecting data from different sheets, after the drop-down list of the “Department” column, the final result is,

final result

Things to Remember

⏩  To avoid errors, remember to check “Ignore Blank” and “In-cell Dropdown”.

⏩  If you want the same drop-down list to appear in multiple cells, select those cells and follow the mentioned procedure.

Conclusion 

Two different ways to create a drop-down list from the different worksheet is discussed in this article. If you have any questions or opinions feel free to comment.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo