Drop down lists from separate workbook

[CJ_C42]

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

[#2]

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.

[#3]

@CJ_C42 

As Mr. Martin_Weiss  pointed out, referencing data from a separate workbook as a source for a drop-down list is not directly supported.

To achieve this, you can follow the following steps:

1. Open both workbooks, the one containing the drop-down list and the one containing the source data.
2. In the source data workbook, select the range of cells you want to use as the source for the drop-down list.
3. Copy the range by pressing Ctrl+C.
4. Switch to the workbook where you want to create the drop-down list.
5. Select the cell or range of cells where you want to create the drop-down list.
6. Go to the Data tab in the ribbon, and click on Data Validation.
7. In the Data Validation dialog box, select the "List" option from the "Allow" drop-down list.
8. In the "Source" field, paste the copied range by pressing Ctrl+V.
• If the source workbook is closed, the formula will include the workbook name and path (e.g., '[WorkbookName.xlsx]Sheet1'!A1:A10).
• If the source workbook is open, the formula will use the workbook name only (e.g., 'Sheet1'!A1:A10).
9. Click OK to apply the data validation and create the drop-down list.

By referencing the range in the source workbook, you establish a connection between the two workbooks, and any changes made to the source data will be reflected in the drop-down list.

Please note that if the source workbook is moved or renamed, the reference in the data validation may need to be updated accordingly.

Additionally, make sure that both workbooks are saved in a location accessible to each other and that the necessary permissions are set to ensure data connectivity between the workbooks.

As Mr. Martin_Weiss has also pointed out, I would not recommend it, but you probably know better than me what is suitable for you.

Happy Excel-ing!

[#4]

Hi @NikolinoDE 

that's a nice trick with copy & paste, I did not know that!

Cheers,

Martin 

[#5]

@NikolinoDE 

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.

[#6]

@NikolinoDE I have applied the drop down list from other worksheet but it is not giving error while entering wrong data

[#7]

@TSmith80 

That is the only thing that has just worked for me. I am so grateful for this comment.