I’m trying to set up a dropdown menu in one of my Google Sheets files, but I want the options to come from a completely different spreadsheet. Specifically, I want to add data validation to cell D5 in my first sheet, and the dropdown choices should pull from cells E2:E12 in my second spreadsheet file. I’ve been looking around but can’t figure out the right way to reference data from another Google Sheets document for this kind of validation. What’s the proper method to link these two files together for a dropdown list?
To create a dropdown menu in Google Sheets that references data from another file, use the IMPORTRANGE function first. This allows you to import the range you need. In your current sheet, enter a formula like =IMPORTRANGE(“spreadsheet_url”, “Sheet1!E2:E12”), replacing the URL with your second sheet’s link. After inputting the formula, Google will prompt you to connect the sheets. Once linked, you can apply data validation to cell D5 by navigating to Data > Data validation and selecting the range you imported. This ensures that your dropdown menu updates when the source data is modified.
The IMPORTRANGE approach mentioned above works well, though I learned the hard way that you need to be careful with permissions. What I typically do is create a dedicated helper column somewhere in my sheet specifically for the imported data, then reference that column in the data validation settings. One thing worth noting is that if the source spreadsheet gets deleted or your access is revoked, the dropdown will break. I also found that IMPORTRANGE can be slow to update sometimes, so if you modify the source data frequently, there might be a slight delay before the dropdown reflects those changes. Make sure both spreadsheets are accessible to anyone who needs to use the dropdown functionality.
yeah you gotta use importrange but make sure you give permission first or it wont work. just put the formula in any empty cell then set data validation to reference that imported range instead of the original file directly