Forum Discussion

CJ_C42's avatar
CJ_C42
Copper Contributor
May 16, 2023

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

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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....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.

    • Ankur2050's avatar
      Ankur2050
      Copper Contributor

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

    • TSmith80's avatar
      TSmith80
      Copper Contributor

      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.

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze 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.

Resources