Forum Discussion
CJ_C42
May 16, 2023Copper Contributor
Drop down lists from separate workbook
I'm trying to create a drop down list where the source data is in a separate workbook but can't seem to find anything in data validation that allows me to use a different workbook as a source. Any ideas how to do this? Thanks
- NikolinoDEGold Contributor
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.
- pjhCopper Contributor
Not working
- Ankur2050Copper Contributor
NikolinoDE I have applied the drop down list from other worksheet but it is not giving error while entering wrong data
- TSmith80Copper Contributor
This step by step doesn't work for me. when i attempt to paste into the source field, nothing happens.
Not sure if this is a change in excel since your post or what.
What if:
1. create a sheet in the target workbook, that is linked to the source workbook data
2. use that new sheet as data validation source, because it's in the same workbook as the validated range.
3. hide the new sheet so it's not cluttering up or getting accidentally modified.
Don't have time to try it right now but it's a thought.
- April_LambertCopper Contributor
- Martin_WeissBronze Contributor
Hi CJ_C42
as far as I know it's not possible to create dropdowns with an external workbook as a source.
And even if it was be possible, I would highly recommend not to do such things. References to external workbooks almost always create trouble, sooner or later.