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 Validationfrom the menu. SetCriteriatoDropdown (from a range)and enterSheet2!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.