Answer

To create a drop-down list from another workbook, use Data Validation with a named range that references the external workbook.

Method 1: Create a Drop-Down List Using a Named Range (Recommended)

A named range creates a stable reference that Excel can read across workbooks.

Step 1: Prepare the source workbook

1. Open the workbook that contains the list values.

2. Place the list values in a single column without blank cells.

3. Select the entire list.

4. Open the Formulas tab.

5. Select Define Name.

6. Enter a clear name without spaces.

7. Confirm the correct cell range.

8. Select OK.

9. Save the workbook.

The source workbook must remain saved for the drop-down list to function correctly.

Step 2: Create the drop-down list in the destination workbook

1. Open the workbook where the drop-down list is required.

2. Select the target column or cells.

3. Open the Data tab.

4. Select Data Validation.

5. Set Allow to List.

6. Enter the formula below in the Source field:

   =SourceWorkbookName.xlsx!NamedRange

7. Select OK.

The source workbook must remain open when creating the validation rule.

Method 2: Create a Drop-Down List Using a Direct Cell Reference

This method works but has a higher risk of breaking when the source file path changes.

Steps

1. Open both workbooks.

2. Select the target cells.

3. Open Data Validation.

4. Set Allow to List.

5. Select the source range directly from the other workbook.

6. Confirm the validation.

Excel automatically converts the selection into a full external reference.

Windows and Mac Differences

Windows

• Data Validation supports external named ranges directly.

• File paths resolve automatically when both files are open.

Mac

• Named ranges work reliably.

• Direct external cell references may fail after closing the source workbook.

• Manual editing of the source field may be required.

Important Requirements and Limitations

1. The source workbook must remain saved.

2. Named ranges must not contain spaces.

3. Structured tables require named ranges for external validation.

4. Volatile functions are not supported in validation sources.

5. External workbook links increase file dependency risk.

Best Practices for Stability

1. Store both workbooks in a fixed folder location.

2. Use named ranges instead of direct references.

3. Avoid blank cells in the source list.

4. Protect the source workbook to prevent accidental edits.

5. Document the data source location inside the destination workbook.

Common Errors and Causes

“The Source currently evaluates to an error.”

This error occurs when the source workbook is closed or the named range no longer exists.

The drop-down list shows no values

This issue occurs when the source range contains blanks or text formatting mismatches.

External reference stops working

This failure occurs after renaming or moving the source workbook.

Supported Excel Versions

• Excel 2019 and later: Full support

• Excel 2016: Full support

• Excel for Mac: Named range support only

• Excel Online: External validation not supported

Security Considerations

External links trigger security prompts when opening files. Excel flags linked workbooks to prevent silent data manipulation. Keeping files in trusted locations reduces warning frequency.

Data Accuracy Impact

Drop-down lists reduce manual entry errors by over 60% and improve data consistency across worksheets. Centralized source lists improve update efficiency and reduce duplication.

Use Cases

• Centralized product lists

• Shared category standards

• Controlled data entry columns

• Multi-file reporting systems

Yes · 0 No · 0 Add Comment · 0

Add ·

Report