Google Docs: create drop down list using data from another spreadsheet

google spreadsheet dynamic drop down list
google sheets - drop down list based on another cell
google spreadsheet drop down list from another sheet
google sheets drop down list color
updating cell values based on selection in drop down list in google spreadsheet
google sheets data validation based on another cell
multi row dynamic dependent drop down list in google sheets.
how to sum values based on selection of drop-down list in google sheets

I need to populate a drop down list in a cell (let's say cell B2) of Spreadsheet A (using data validation) on basis of data located in Spreadsheet B (range - C3:C15). How do I do that? Googled this for several hours - no luck. Thank you.

Google Docs: create drop down list using data from another , Create in-cell dropdown data validation with a list from a range of another workbook 2 We will be using custom filter views for each role involved, and because different people (both inside and outside my organisation) will be using the workbook, I want to make Sheets, Microsoft Windows, Business. Create a drop-down list Open a spreadsheet in Google Sheets. Select the cell or cells where you want to create a drop-down list. Click Data Data validation.

The format to access cells from another spreadsheet in Google Sheets is:


For example, let's say you have a Google Sheet that contains 2 spreadsheets named: Sheet1 and Sheet2 (The names are listed on the tabs at bottom left hand side of each sheet).

In Sheet1 if you wanted to access cell B2 in Sheet2, you reference it by inputting: Sheet2!B2

In Sheet2 if you wanted to access cells C3:C15 in Sheet1 , you reference those cells by inputting: Sheet1!C3:C15

To specifically add cells from another sheet to a dropdown:

1) Select the cell you want the dropdown in

2) Right click on the cell and select Data Validation

3) In the dialog box, click the grid image in the Criteria input box

4) This will bring up the "What Data?" dialog box

5) Click on the tab for the sheet you want to access

6) Hold down shift and click on the cells you want to select (you will see the cell addresses show up in the input box in the "What Data?" dialog)

7) Click OK and you are set. The data will update if you make changes in the source sheet.

More info:

Create in-cell dropdown data validation with a list , Data validation drives consistency in your spreadsheets and makes On August 26, 2010, Google Docs spreadsheets (aka Google Sheets) added in-cell dropdown Simple – it constrains the values of a cell to a pre-set list or specific range. In this way, you make your Google Docs spreadsheet more  Now to the topic, i.e. how to create an in-cell drop-down menu from multiple ranges in Google Sheets. For this, I am going to use the above two approaches. Let me begin with approach 1 above which is more flexible. Data Validation Drop-Down Range in Two or More Columns List from Range Approach (Approach 1)

Similar to rossmcm's answer but with a few tweaks because his answer didn't work for me:

=IMPORTRANGE(spreadsheet_url; range_string)

Where spreadsheet_url is The full URL of the spreadsheet from where data will be imported, and range_string a string, of the format "[sheet_name!]range" (e.g. "Sheet1!A2:B6" or "A2:B6") specifying the range to import.


=IMPORTRANGE("", "Sheet1!A1:A7")

More info from Google DOCS Help!

How-To Add Google Docs In-cell Dropdown and Validation in , be added to a cell, start restricting entries with dropdown lists in Google Sheets​. Sheets, it can get frustrating if people are entering the wrong data. List from a range: You can select a range of cells in your spreadsheet to  There are the names of two authors in the range C2: D2. This range we can use to create a drop-down list. See the list of their books in the range C3: D10 that we can use to create a dependent drop-down list. We can use the data in Cell C2: D2 for the drop down as below.

Google Sheets, We use drop-down lists in the Google sheet to enter data from a predefined list of create or modify a drop-down menu using an Excel data validation list based on To create a drop-down menu from another workbook, we will need 2 named​  Now you can select H2:H instead of D2:D in the “List from a range” in Data Validation in Google Sheets. Here is another tutorial, in line with the above, as an addition to my best Data Validation examples in Google Sheets – Distinct Values in Drop Down List in Google Sheets.

How to Create a Dropdown List in Google Sheets, Another (advanced) use of the drop-down list can be to extract data for the selected item. For example, suppose you have a list of students and  To create a data validation drop-down list (Google Sheets): Right-click the cell where you want the drop-down list to appear and select Data validation. On our "front-end" worksheet—that is, the worksheet that contains the form, we provide a label for the drop-down in B5.

How to add, edit and create drop-down lists in Google Sheets , Today marks the release of the first-ever electronic spreadsheet from a G Suite Pro Tip: how to create a dropdown list in Google Sheets (and To commemorate​, we're releasing another G Suite Pro Tip to help you using two key features in Google Sheets: Data Validation and Conditional Formatting. You can also use drop-down lists in Google Spreadsheets to create interactive charts, to-do lists, and dashboards. For example, you can create a drop-down with a to-do list where the user can

  • Is importing the data from the other workbook into this workbook really the only way to do it? I would like to avoid adding another sheet that does nothing except duplicate data from another workbook.
  • As far as I know, you can't get the drop-down items directly from another workbook, you need to import them into the workbook that you want to use them in. A bit of a drag maybe, but getting the data from another workbook every time the user clicks the chevron is probably a bit more intensive than getting it from the same workbook. Bear in mind that the validation source data range needn't be on another sheet - it can be the same sheet - i.e. the range could be Sheet1!B1:B20 instead Sheet2!A1:A20 but I think it's better to keep it out of sight/out of the way.
  • Sorry, probably I didn't express myself clear enough in the question. What I ment is: I needed to take data from Book A and use it for populating dropdown menu in Book B (another file on my Google drive), not use data from another sheet of the same book (file). That project is complete - we used another approach. Thank you for your answer - it is correct for the stated question.
  • @keshet, you should accept rossmcm's answer. It is appropriate to situation.
  • This does not seem to work anymore: Sheet1!C3:C15. I'd like to reference a range from another worksheet.