How to Create Drop Down List from Another Workbook in Excel

Drop-down lists in Excel are a great way to make data entry more efficient and accurate. By using a drop-down list, users can select predefined options instead of typing in the data manually, reducing the chances of errors and inconsistencies. In this guide, we’ll go through a step-by-step method for creating a drop-down list in Excel from another workbook. This can be useful if you have a list of options that you use frequently, and you want to keep that list in a separate workbook for easier management and updating. With this method, you can create a drop-down list that references the list in the other workbook, ensuring that any changes made to the original list are automatically reflected in the drop-down list.

Download Practice Workbook

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

What Is Drop-Down List in Excel?

If you want to select a value from a predefined list of options, drop-down lists in Excel are a powerful tool to do such things. These lists are commonly used to ensure consistent and accurate data entry in spreadsheets. A drop-down list in Excel appears as a small rectangular box that displays a list of options when clicked or selected. Users can then select an option from the list, which populates a cell with the chosen value. Excel drop-down lists can be customized with various features, such as sorting options, making the list searchable, and adding or removing options. Overall, drop-down lists in Excel are a simple and effective tool for improving data entry accuracy and efficiency.

Step-by-Step Method to Create Drop-Down List From Another Workbook in Excel

In this section, we are going to explain a step-by-step method to create a drop-down list from another workbook.

• First, we have to 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.

• Now we are going to 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. Then select Define Name.

• Now we are going to 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.

• Finally, we 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. In order to do so, we are going to use the following method on the dataset given below.

We need to utilize the UNIQUE function in the process.

• Now we are going to 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.

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

• Now we are add source =INDIRECT(“Table1[Unique Carname]”) in Data Validation source.

• Now finally we get a drop-down list in the H5 cell.

How to Create Drop-Down List From Another Sheet

In order to create a dynamic worksheet we have to follow the following steps with the data given below.

Here we have to add a Data Validation source from another sheet.

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

• Finally, we 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.

Conclusion

In conclusion, creating a drop-down list in Excel from another workbook can be a useful tool for improving data entry efficiency and accuracy. When creating a drop-down list from another workbook, it’s important to remember to have the source workbook open, name the range you want to use, use the correct formula syntax to reference the range, test the drop-down list, and use a dynamic named range if necessary. Keeping the source workbook and the workbook containing the drop-down list in the same location, and protecting the sheet containing the drop-down list, are also important things to remember. By following these tips and best practices, you can create a drop-down list that references another workbook and helps streamline your Excel workflow.