How to Create a Drop Down List from Another Sheet in Excel

This tutorial will demonstrate how to create a drop-down list from another sheet in Excel. 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.

1. Creating Excel Drop-Down List from A Single Worksheet

In this case, our goal is to create a drop-down list from another sheet in Excel. To do so, we will use a single worksheet and follow the below steps of this method.

First, 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.

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

Now, we will make a drop-down list of the names. Select cell “B5”, go to “Data” and click on the “Data Validation” option. In Short: B5→Data→Data Validation.

After that, a new window will pop up that shows how we want to have our drop-down list. Now click on this icon and select the “List” option.

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

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

In addition, we are now on our “Source Data” sheet. Select the data from the column “Name” and click on the data validation icon to confirm the selection.

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

Furthermore, our drop-down list is ready. Click on this icon to show the list.

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

Again, do the same steps to find the “Department” column in the drop-down list and you will get the following result.

Thus, you have created a drop-down list from another sheet in Excel for a single worksheet.

2. Creating Excel Drop-Down List from Multiple Worksheets

Now, we want to create a drop-down list from another sheet in Excel. This time, we want to do this for multiple worksheets and to do so we have to follow the below steps of this method.

Firstly, 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.

Again, for the “Subject” data create another worksheet, “Data 2”.

And for the “Department” column, create another worksheet, “Data 3”.

Then, make a new table in a different worksheet where the drop-down lists of these data will be created.
Following this, we will first make a drop-down list for the “Name” column. Select the cell range, go to “Data” and click on the “Data Validation” option.

Simultaneously, a new window appeared, and click on this icon to open a chart where different styles of the drop-down lists are given. Select “List”.
Concurrently, make sure to check “Ignore Blank” and “In-Cell Dropdown”. Click on this source icon to continue.

At this point, 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. Select the data and click on the icon to the right in the Data Validation window to continue.

Thereafter, in the new window, the final selection is given. Click “OK” to proceed.

In addition, we have got our drop-down list for the “Name”.

Next, do the same for the “Subject” and “Department” columns.
Lastly, the final selection for the “Subject” column is similar to the below image.

Lastly, the drop-down list for the “Subject” column will be like the following image.

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

Hence, you have created a drop-down list from another sheet in Excel for multiple worksheets.

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.

Download Practice Workbook

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

DropDown List.xlsx

Conclusion

Henceforth, follow the above-described methods. These methods will help you to create a drop-down list from another sheet in Excel. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

How to Create a Drop Down List from Another Sheet in Excel

1. Creating Excel Drop-Down List from A Single Worksheet

2. Creating Excel Drop-Down List from Multiple Worksheets

Things to Remember

Conclusion

Further Readings

Leave a reply Cancel reply