Forum Discussion
Drop down lists from separate workbook
As Mr. Martin_Weiss pointed out, referencing data from a separate workbook as a source for a drop-down list is not directly supported....and as Mr. TSmith80 suggested, this would be a good workaround.
To achieve the suggestion of Mr. TSmith80, you can follow the following steps:
Step 1: Open Both Workbooks
Open the source workbook (where your data is stored).
Open the target workbook (where you want to create the drop-down list).
Step 2: Create a Linked Sheet in the Target Workbook
In the target workbook, add a new worksheet (e.g., name it LinkedData).
In the first cell of this new sheet (e.g., A1), create a formula that links to the source data:
Example formula for the first cell:
='[SourceWorkbook.xlsx]SheetName'!A1
Replace SourceWorkbook.xlsx with the actual name of your source workbook.
Replace SheetName with the name of the worksheet in the source workbook containing the data.
Drag the formula down or across to replicate the entire range from the source workbook into your LinkedData sheet.
Step 3: Use the Linked Sheet as the Data Validation Source
Go to the cell(s) in the target workbook where you want the drop-down list.
Open the Data Validation dialog:
On the ribbon, go to Data > Data Validation.
In the Allow field, choose List.
In the Source field, reference the linked range in the LinkedData sheet:
Example:
=LinkedData!$A$1:$A$10
Adjust the range ($A$1:$A$10) to match the size of your data in the linked sheet.
Hope these steps can support the suggestion of Mr. TSmith80.
