Google Docs: creating a drop-down list using data from another spreadsheet

Question

I need to populate a drop-down list in the cell (say cell B2) of table A (using data validation) based on the data located in table B (range - C3: C15). How can I do it? For several hours he looked for it, no luck. Thanks.

Answer 1

Getting items from another book, unlike another sheet in one book, is similar. This is a two-step process. First, you need to import the data that you want to use for the validation elements into the workbook where you want to use it, then plug it in as described in @ uselink126's answer.

Example: Workbook 2 contains a list of fruit names in a specific order. For reading, a named range of Fruits been assigned to the list, but this is optional. Workbook 1 , in Sheet 1 there is a column of cells in which we want to populate the drop-down list with elements from book 2.

Step 1 - Import Data

• Add another sheet to book 1 and paste the following formula into cell A1:

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

where <key> is the unique identifier of the Google documents assigned when creating the spreadsheet. In this example, items are sorted alphabetically as part of the import, and for this you must enter:

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

1, means column 1 is sorting, true means ascending sorting. The cells in column 1 should be filled with sorted fruits.

Step 2 - Send data validation to the imported list

In Workbook 1, Sheet 1, select the cells in which you want the fruits to be their drop-down data source. - Right-click the selection and select Data Validation from the menu. Set Criteria to List from a range and enter Sheet2!A1:A20

What is it. Drop-down chevrons should appear in these cells, and when you click on them, a list of fruits appears.

Please note that this is “live” - adding a fruit item to the Workbook 2 list will also add it to the drop-down list.

Answer 2

The format for accessing cells from another table in Google Sheets:

 SheetName!CellAddress 

For example, let's say you have a Google leaflet containing two tables: Sheet1 and Sheet2 (the names are shown on the tabs at the bottom left of each sheet).

In Sheet1 , if you want to access cell B2 in Sheet2 , you refer to it by typing: Sheet2!B2

In Sheet2 , if you want to access cells C3:C15 in Sheet1 , you reference these cells by typing: Sheet1!C3:C15

To specifically add cells from another sheet to the drop-down list:

1) Select the cell you want to display in

2) Right-click on the cell and select "Data Validation"

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

4) This will cause "What data?" dialog window

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

6) Hold down the shift key and click on the cells that you want to select (you will see that the addresses of the cells are displayed in the input window in the "What data?" Dialog box)

7) Click "OK" and you are installed. The data will be updated if you make changes to the source text.

Additional information: https://support.google.com/docs/answer/186103?hl=en