If your list and dropdown are on different pages, you will need to know this trick to make the dropdown list dynamic.
Giving users choices using a drop-down list is a good way to avoid typing errors, since the user can’t type anything other than one of the items in the drop-down list. These drop-down lists also make data entry in general easier. Clicking and choosing is faster than manually entering values from the keyboard.
Fortunately, Microsoft Excel’s Data Validation feature lets you create a drop-down list, and the process is quick and easy as long as the list data is on the same sheet as the drop-down list. You can reference a list on another sheet, but it takes a bit more work. In this tutorial, I will show you how to populate a drop down list with data on another sheet.
I’m using Microsoft 365 desktop on a 64-bit Windows 10 system, but you can use older .xlsx versions. Excel for the web supports an existing drop-down list, but you cannot use Excel for the web to create the drop-down list.
Throughout this article, I will refer to the data validation control as a drop-down list. This technique will not work with content controls or the legacy combo box control.
SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
How to fill from another sheet in Excel
You can populate a dropdown list with a list on another sheet by referencing the cells, but the resulting dropdown list will not be dynamic, even when the list is inside an array object. Instead, you must apply a name to the list of items (except the table header cell), and then refer to the list name when creating the drop-down list on a another sheet. It’s a piece of the puzzle that would otherwise drive you crazy if you didn’t know it.
Figure A displays a short list of animals on a sheet named Lists. The name of the table is Animals, but we won’t reference it that way. In a large application, you may have many lists on this sheet, and many drop-down lists may refer to different lists on this sheet.
Before continuing, I want to list the different elements because the term animal comes up a few times and it is easy to confuse them:
- Animals: the name of the table with the list of animals. It is not necessary to name the table for this technique to work. This table includes the whole list, including the header cell.
- Animals_List: The named range of list items, excluding the header cell, of the animal list.
- Animal list: the only column in the animal table.
First, I’ll show you how to name the list, Animal_List – don’t confuse this with the table name (Animals):
- Select list items only, do not include the header cell. In this case, it’s B3:B6.
- Click the Formulas tab.
- In the resulting dialog, name the range Animals_List.
- Leave the Scope setting on Workbook.
- Click inside the Source control and enter =Animals[Animals List]as shown on the Figure B.
- Click OK.
The name of the source list range deserves some explanation. The first component, =Animals, refers to the array named Animals on the Lists sheet. The second part, [Animals List] is the column name. Subsequently, this reference, =Animals[Animals List], refers to the Animal List column in the Animals table. If you’re not using a table, the reference is much simpler, Lists!$B$3:$B$6.
Now, let’s create a dropdown on another sheet named Dropdown and reference Animals_List:
- Create a second sheet and name it Dropdown (strictly speaking, there’s no need to name this new sheet).
- Select a cell – I selected B2.
- Click on the Data tab.
- In the Data Tools group, click Data Validation, then click Data Validation from the drop-down list.
- From the Allow drop-down list, choose List.
- For Source, enter =Animals_List, as shown in Figure C.
- Click OK.
As you can see in Figure DExcel populates the drop-down list using the list of animals on the Lists sheet – a completely different sheet, but in the same workbook.
If you change the list of animals on the List sheet, the drop-down list will automatically update accordingly. Try this now.
Return to the Animals table on the Lists sheet and add the rabbit. Next, check the drop-down list on the Drop-down List sheet. It will include the bunny!
If that doesn’t work for you, be sure to expand the table to enter the new record by selecting the last table cell and pressing Tab to create a new row.
Seems like a lot of work, but it’s necessary if the list and drop-down list aren’t on the same sheet. In a nutshell, you need to define the list by giving it a range name. Do not include the header cell in this name. Then use the named range as the source reference for the drop down list. As long as the Scope parameter is the entire workbook, everything will update as expected, even if the list is on another sheet.
Populating a drop-down list with a named range on another sheet isn’t difficult, but it does require a few unexpected tasks. In a future post, I’ll show you how to populate a drop-down list using a list in another workbook.