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

Question

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.

Answer 1

Getting the items from another workbook, as opposed to another sheet in the same workbook is similar. It's a two-step process. First, you need to import the data you want to use for the validation items into the workbook where you want to make use of it, then connect it up as described in @uselink126's answer.

An example: Workbook 2 contains a list of fruit names in no particular order. The list has been assigned a named range Fruits for readability, but this isn't necessary. Workbook 1, Sheet 1 has a column of cells where we want to populate a drop-down with the items from Workbook 2.

Step 1 - Importing the data

• Add another sheet to Workbook 1 and insert the following formula into cell A1:

  =ImportRange("<key>","Sheet1!Fruits")
  

where <key> is the unique ID Google docs assigned when you created the spreadsheet. In the example, the items are sorted into alphabetical order as part of the import, and to do this you would enter instead:

    =Sort (ImportRange("<key>","Sheet1!Fruits"), 1, true)

The 1, signifies column 1 is what to sort by, true means sort ascending. The cells in column 1 should populate with the sorted fruits.

Step 2 - Point the data validation to the imported list

On Workbook 1, Sheet 1, Select the cells you want to have the fruits as their drop-down data source. - Right-click the selection and click on Data Validation from the menu. Set Criteria to List from a range and enter Sheet2!A1:A20

That's it. The drop-down chevrons should appear in those cells and when clicked the list of fruits should appear.

Note that this is "live" - adding an item of fruit to Workbook 2's list will also magically add it sorted in the drop-down list.