How to Create Drop Down List from Another Workbook in Excel

• Create a master dataset and save the file as “Master Dataset”. You can name it anything you want. Our workbook’s name is Master Dataset.

• Create another workbook named “Secondary Dataset” to extract the Product column from “Master Dataset”. To do so we are going to Formulas and then Defined Names of the Secondary Dataset. Select Define Name.

• Select the Product Column in the Product Reference or give the code below ='[Master Dataset.xlsx]Sheet1′!$D$5:$D$10 . They both are the same thing and Master Dataset.xlsx must be open simultaneously.

• Here is a better view of adding a New Name.

• Get a dropdown list from another book.

How to Create Dynamic Drop-Down List

We are going to create a dynamic drop-down list from the same sheet. To do so, we will use the following method on the dataset given below.

Utilize the UNIQUE function in the process.

• Create a column named “Unique Carname” from the Car Name After using the UNIQUE function in F5 we are going to drag the fill button from F5 to F8.

• Turn the Unique Carname into a table. We are going to name it TABLE 1

• Add source =INDIRECT(“Table1[Unique Carname]”) in Data Validation source.

• Get a drop-down list in the H5 cell.

How to Create Drop-Down List From Another Sheet

Create a dynamic worksheet we have to follow the following steps with the data given below.

Add a Data Validation source from another sheet.

• To see the source dataset in detail, we have given the screenshot below.

• Get the validation in another sheet.

Frequently Asked Questions

Q: Can I create a drop-down list from another workbook if it’s not open?

No, you need to have the source workbook open in order to create a drop-down list from it. If you try to create a drop-down list while you have closed the source workbook, Excel won’t be able to find the range you’re referencing, and the drop-down list will be empty.

How do I reference the range in the source workbook for the drop-down list?

To reference the range in the source workbook for the drop-down list, you need to use a combination of the workbook name, sheet name, and cell range. The formula should look like this:

='[workbook name.xlsx]sheet name’!$A$1:$A$10Replace “workbook name.xlsx” with the actual name of the source workbook, “sheet name” with the name of the sheet containing the range you want to use, and “$A$1:$A$10” with the cell range you want to reference.

Q: What happens if I make changes to the source workbook after creating the drop-down list?

A:If you make changes to the source workbook after creating the drop-down list, the changes will be automatically reflected in the drop-down list. This is because the drop-down list is referencing the range in the source workbook, so any changes made to that range will be updated in the drop-down list.

Q: Can I create a drop-down list from a range that’s on a different sheet in the same workbook?

A:Yes, you can create a drop-down list from a range that’s on a different sheet in the same workbook by using the sheet name in the range reference. The formula should look like this:

=’sheet name’!$A$1:$A$10Replace “sheet name” with the name of the sheet containing the range you want to use, and “$A$1:$A$10” with the cell range you want to reference.

Q: Can I create a drop-down list that allows users to add new options?

Yes, you can create a drop-down list that allows users to add new options by using a dynamic named range. This allows the range to expand as new options are added, so you can include them in the drop-down list. To do this, you need to use the OFFSET and COUNTA functions to define the named range.

Things To Remember

1. The source workbook needs to be open in order to create the drop-down list.
2. You need to name the range you want to use for the drop-down list in the source workbook.
3. When referencing the range in the source workbook, use the workbook name, sheet name, and cell range in the formula.
4. Make sure the range reference is an absolute reference (using $) so that it doesn’t change if you copy the formula to other cells.
5. Test the drop-down list to make sure it’s working correctly, and check that any changes made to the source workbook are reflected in the drop-down list.
6. If you want to allow users to add new options to the drop-down list, use a dynamic named range that expands as you add any new options.
7. Keep the source workbook and the workbook containing the drop-down list in the same location, and make sure they stay there, to avoid broken links.
8. If you move the source workbook or rename it, you’ll need to update the range reference in the formula for the drop-down list to continue working.
9. Consider protecting the sheet containing the drop-down list to prevent accidental changes.

Download Practice Workbook

You can download the Excel workbook that we used to prepare this article.