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.
Here is a quick view of our task
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
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.
Create a new table in a different worksheet containing columns “Name”, “Subject”, and “Department” where we will create the drop-down list.
Now first we will make a drop-down list of the names. Select cell “B4”, go to “Data” and click on “Data Validation”
A new window popped that shows how we want to have our drop-down list. Now click on this icon and select “List”.
Make sure “Ignore Blank” and “In-cell Dropdown” are checked. Now click on this icon to select your drop-down list data from a different sheet.
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.
We are now on our “Source Data” sheet. Select the data from the column “Name” and click on this icon to confirm the selection.
Now that our drop-down data is ready, click “OK” to confirm.
And our drop-down list is created. Click on this icon to show the list.
Now do the same steps for the rest of the two lists. The “Drop-Down” list for the “Subject” column is this
And for the “Department” column the drop-down list is,
So, the final result is,
2. Drop Down List from Multiple Worksheets
We can also make a drop-down list from multiple worksheets. This procedure is discussed below,
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.
For the “Subject” data,
And for the “Department” column, another different worksheet is made.
Make a new table in a different worksheet where the drop-down lists of these data will be created.
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→ Data Validation
A new window appeared. Click on this icon to open a chart where different styles of the drop-down lists are given. Select “List”.
Make sure to check “Ignore Blank” and “In-Cell Dropdown”. Click on this icon to continue.
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.
Now we are on a different sheet where the data are given. Select the data and click on this icon to continue.
In the new appeared window the final selection is given. Click “OK” to proceed.
And we have got our drop-down list for the “Name”.
Now do the same for the “Subject” and “Department” columns.
The final selection for the “Subject” column is,
The drop-down list for the “Subject” column,
And after collecting data from different sheets, after the drop-down list of the “Department” column, the final result is,
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.
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.