Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Create Drop Down List From Another Workbook In Excel?
Microsoft Excel is a powerful tool for organizing and analyzing data, and one of its most useful features is the ability to create drop-down lists. Drop-down lists are a great way to ensure consistency and accuracy in your data entry, as they allow users to select a value from a pre-defined list instead of typing it manually.
In this tutorial, we will focus on how to create a drop-down list in Excel by referencing data from another workbook. This technique can be particularly useful if you have a large amount of data that you want to keep separate from your main workbook, or if you want to share the same list across multiple workbooks. We will walk through the steps to create the list in the source workbook, link it to the destination workbook, and finally add the drop-down list to a cell. By the end of this tutorial, you will have a solid understanding of how to create and use drop-down lists in Excel, and how to link them between workbooks. Let's get started!
Create Drop Down List From Another Workbook
Here we will first name the source cells, then create a data validation list with the name as a formula to complete the task. So let us see a simple process to know how you can create drop-down lists from another workbook in Excel.
Step 1
Consider an Excel sheet where you have a list of names similar to the below image.
First, select the range of cells in the source sheet and click on the formula and select Define name, then give the name Company, and click OK.
Select cell > Formulas > Define names > Name > Ok.
Step 2
Then in another sheet, click on formulas and click on define name. Then enter the name and enter it as "Source.xlxs!Company" and click OK.
Formulas > Define names > Name > Refers to > Ok.
Step 3
Finally, click on an empty cell, then click on data and select data validation. Then set allow to list and source as "=Drop" and clicked OK to complete our task.
Empty cell > Data > Data validation > Allow > Source > Ok.
The final output will be similar to the below image.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can create a drop-down list from another workbook in Excel to highlight a particular set of data.
1K+ Views
