Data validation from another workbook Google Sheets

In Microsoft Excel, you can create a drop down Data Validation list, so it’s easy to enter valid items in a cell. Usually, the list of valid items is stored in the same workbook, but it’s possible to set up a drop down from a list in another workbook. NOTE: Both workbooks must be open, any time you want to use the drop down lists.

Inhaltsverzeichnis Show

Introduction
Video: Drop Down List from Another Workbook
Create Workbook With Master List
Create a Reference to Master List
Define Name for Master List
Create the Drop Down List
Use the Drop Down List
Get the Sample Files
More Tutorials
List of items vs. List from range
Applying drop-down lists to multiple cells at once
How to create a drop-down list in Google Sheets
List from a range of cells to create a drop-down list
Multiple ways to enter / select the range
Using the UNIQUE function to create a criteria list
Show warning vs. List from range
How to edit a drop-down / data validation
How to copy drop-down lists to other cells, and down the column
Remove drop-down lists and data validation
Create a drop-down from another sheet
Color cells based on drop-down selection (Conditional Formatting)
Pop Quiz: Test your knowledge
Question #1
Question #2
Question #3
Question #4
Question #5
Answers to the questions above:

Introduction

A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. You can try these steps in your own workbooks, or download the sample files, with the dropdowns, for this tutorial.

IMPORTANT: For the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists. You could create the list in a workbook that is always open, but hidden, such as the Personal workbook.

Video: Drop Down List from Another Workbook

To see the steps for creating the data validation drop down from a list in another workbook, watch this short Excel video tutorial. The written steps are below the video.

Create Workbook With Master List

The first step is to create a master workbook, and a master list of items, in that workbook. In this example:

The master list in a workbook named DataValWb.xlsx
The master list contains customer names, in a range named CustName

Tip: For instructions on creating a named range, go to the Naming Ranges page.

If you are using your own file with a master list, substitute your master file's name and list name in the instructions below.

Create a Reference to Master List

Next, follow the steps below, to create a named range in the workbook where the data entry drop down list will be added.

To start, make sure the master workbook is still open — DataValWb.xlsx in this example.
Next, open the workbook in which you wish to use the list in Data Validation
- Or, create a new workbook for the drop down lists.
On the Excel Ribbon, click the Formulas tab
Next, click the Define Name command

Define Name for Master List

When the New Name dialog box opens, follow these steps:

Type a name for the List, e.g. MyCustList
From the Scope drop down, select Workbook
Click in the Refers to box — this is where you’ll type a reference to the named range in the source workbook
Type an equal sign, then the source workbook name and extension. Do NOT include any square brackets in the name. For example:
- =DataValWb.xlsx
- OR, if the workbook name includes spaces, start and end the name with an apostrophe. For example: ‘DataVal May10.xlsx’
Next, type an exclamation mark — !
Finally, type the range name in the source workbook
The completed reference should look like this:
=DataValWb.xlsx!CustName
OR =’DataVal May10.xlsx’!CustName

Create the Drop Down List

Next, you can create one or more drop down lists, based on the name that you just created.

Select the cells where you want the drop down lists.
On the Ribbon, click the Data tab
In the Data Tools group, click the Data Validation command
In the Data Validation dialog box, go to the Settings tab
Click in the Allow box
In the drop down list of options, choose List
Click in the Source box
Press the F3 key, to open the Paste Name dialog box
In the list of names, click on MyCustList
Click OK, to close the Paste Name dialog box
The selected name appears in the Souce box
Click OK button to close the Data Validation dialog box.

Use the Drop Down List

To use the data validation drop down lists, both workbooks must be open.

First, open the workbook that contains the master list.
Open the workbook that contains the drop down lists
Select an item from one of the the data validation drop down lists.

Get the Sample Files

Get the zipped Drop Downs from Other Workbook sample files. There two files and both are in xlsx format. The files do not contain macros.When you unzip the files, keep both files in the same folder. Open master file first (DataValWb.xls)

List of items vs. List from range

When creating a drop-down list, you will have the option to base your drop-down list on a "List of items", or you can choose to "List from a range".

When you choose "List of items", this means that you will type the items in the list directly into the data validation menu, separated by commas. To edit the list, you will need to visit the data validation menu each time.

If you choose "List from a range", this means that you will type the items in the list into cells in your spreadsheet, and then you will type the range / cell address that contains the list. With this method, you can modify your list by editing the contents of the spreadsheet cells.

Applying drop-down lists to multiple cells at once

If you select a range of cells instead of a single cell before opening the data validation menu (or if you type the address of a range of cells in the "Cell range" field), the data validation settings / drop-down lists will be applied to the entire range of selected cells. You can also copy and paste drop-downs from one cell into other cells, which I will show you later.

How to create a drop-down list in Google Sheets

First let's go over an example of creating a drop-down list in Google Sheets, based on a list of items.

Below we have an example that shows a list of students, and an empty column beside it, where we want to create a drop-down list to enable selecting each student's attendance status. We will make a simple drop-down list that allows the user to select between two choices (Present, and Absent). Follow the instructions below to learn how to do this.

Select the cell / range of cells where you want your drop-down list to be. This example demonstrates creating a drop-down in cell B2.

On the top toolbar menu, click "Data". This will open a menu below. Then click "Data validation". This will open the data validation menu, which is the place to create a drop-down list.

Another way to open the data validation menu, is to right-click on the cell, then click "View more cell actions", and then click "Data validation".

Under the "Criteria" selection, select "List of items". (You can also select "List from a range", but this example demonstrates the "List of Items" option.

Notice that the cell range appears like this: Sheet1!B2. This could be typed manually, but Google Sheets automatically filled it in because we selected the appropriate range before opening the menu.

Type the items that you want in your drop-down list, separated by commas, like this: Present,Absent

Spaces are not needed after each comma.

Choose between "Show warning" or "Reject input". This example demonstrates the "Show warning" option.

Click "Save".

As shown below, after applying data validation, a triangular arrow appears inside of a small box, on the right side of the cell. This indicates that a drop-down list is present. If you hover your cursor over the small box with the arrow, the box will turn grey. If you click the arrow, the drop-down list / menu will open.

After clicking the drop-down button, the items that you typed in the data validation menu will appear in your drop-down list. When you click on one of the items in the list, Google Sheets will automatically fill the cell with the text / value that you selected.

Note that if you had selected the range B2:B8 before opening the data validation menu (Instead of only cell B2), the drop-down lists would have been applied to cells B2 through B8.

List from a range of cells to create a drop-down list

Now let's go over an example where we will list the items for the drop-down list, in spreadsheet cells. We are using another example of student attendance, but this time we will add in a couple more items / selections to the list.

When you list your drop-down items in spreadsheet cells, it makes it very fast and easy to modify the list of items for your drop-down list. When you edit the list that is entered into the spreadsheet cells, the list in the drop-down will automatically reflect the change. This also allows you to use formulas that modify the list of items, which I will go over further below.

As you can see below, the items are entered into the range D2:D5. The following words are entered into cells D2 through D5: Present, Absent, Tardy, Excused. We are going to refer to this range of cells in the data validation menu, to base our drop-down list on the list of items in column D. To do this, follow the instructions below.

Select the cell / range of cells that you want to put drop-down lists in. This example demonstrates creating a drop-down in cell B2.

On the top toolbar, click "Data", then click "Data Validation".

Under the "Criteria" option, select "List from range".

Type the range of cells that contains the list for your drop-down (Or you can use the "Select data range" button to specify the range without typing it manually. This button looks like a small grid). In this example we are typing the range D2:D5.

Choose between "Show warning", or "Reject input", and then click "Save".

As shown below, after applying data validation to cell B2, with the "List from a range" field referring to the range D2:D5, a drop-down list has appeared in cell B2, and the items that are listed in column D are also listed in the possible selections for the drop-down menu.

Multiple ways to enter / select the range

There are two different ways to specify the cell / range of cells that you want the data validation to be applied to… or in other words the cells that you want the drop-down lists to be in. First, you can select the appropriate cell / range of cells before opening the data validation menu, as described in the examples in this lesson. When you do this, Google Sheets will automatically fill in the data range address. Or, you can type the cell address directly into the data validation menu, under the "Cell range" field. For example, if the data validation is applied to cell B2 in the tab named "Sheet1", then the cell range in the data validation menu will appear as Sheet1!B2

There are two different ways to specify the range that contains the list items for the "List from a range" option. First, you can type the address directly into the data validation menu, or as is shown below, you can use the "Select data range" button.

To select the data range instead of typing it, click the button that looks like a grid (Says "Select data range" when you hover your cursor over it). A small menu will pop up.

Then select the cell / range of cells that contains the items for the drop-down list. Then click "OK".

Using the UNIQUE function to create a criteria list

A great trick to use with drop-down lists in Google Sheets, is to use the UNIQUE function to generate a list of items for your drop-down menu, when there are duplicates present in the column that you are referring to. In the example below, you can see in column A that there are duplicate names. If we want to use this column to find a list of unique (non-duplicate) names so that we can refer to it with a drop-down list, we can simply use the UNIQUE function as shown below.

In cell E2, we are using the UNIQUE function to remove the duplicates from column A, which gives us a list of unique names in column E. We can then refer to column E in the data validation menu, to use the unique list of names as the drop-down list items.

Formula: =UNIQUE(A2:A)

Show warning vs. List from range

You can choose how you want your spreadsheet to respond when someone enters invalid data into a cell with a drop-down / data validation.

The first option is to "Show warning" when invalid data is entered, which will display a warning in the cell (Small red triangle with a message that pops up when you hover over it). In the images below you can see where this option is selected in the data validation menu, as well as the warning that displays when invalid data is entered with the "Show warning" option.

The second option is "Reject input." With this selected, when invalid data is entered, Google Sheets will reject the input, and a message will pop up that says "There was a problem". You can see this demonstrated in the images below.

How to edit a drop-down / data validation

To edit the data validation for an existing drop-down, simply select the cell / range of cells that you want to edit the data validation of, and then open the data validation menu as described in the examples above, by clicking "Data" and then clicking "Data validation". Then modify any settings that you want, and then click "Save".

How to copy drop-down lists to other cells, and down the column

As mentioned earlier, if you specify a range of cells in the "Cell range" field of the data validation menu, the drop-down lists will be applied to the entire range of cells. But if you have a drop-down list in one cell that you want to copy / apply to other cells, you can do this easily by copying and pasting a cell that contains a drop-down. You can also use "Autofill" to do this.

If you copy a cell that has a drop-down in it, you can paste it into another cell, and the drop-down will be applied to the cell where you pasted. As shown below, when you copy a cell, a dotted line will appear around the border of the cell.

To copy a drop-down in Google Sheets, select a cell that has a drop-down in it, press Ctrl + C on the keyboard to copy the cell, then select the cell / range of cells where you want to copy the drop-downs to, and then press Ctrl + V on the keyboard.

You can also use Autofill to copy drop-downs. As shown below, when you hover your cursor over the bottom right of the selected cell, the "Fill Handle" appears, which looks like a plus sign. When the fill handle appears, click, hold the click, and drag your cursor downwards until you reach the end of where you want your drop-downs, then release your click.

After either copying and pasting or using autofill to copy your drop-downs, the entire range that you pasted into / filled will have drop-down lists, as shown below.

Remove drop-down lists and data validation

So now that you know how to create drop-down lists, let's go over how to remove them. There are two different ways to remove drop-downs / data validation in Google Sheets. The main way is to open the data validation menu, and to click "Remove validation". You can copy a cell that does not have a drop-down in it, and then paste it into the cell that contains the drop-down you want to remove.

To remove a drop-down in Google Sheets, select the cell or range of cells that contains the drop-down, then on the top toolbar click "Data", and then click "Data validation". Click the button that says "Remove validation".

As you can see in the image above, a drop-down list is present in cell B2.

But after following the instructions above, the drop-down list is removed from cell B2, as shown below.

Yet another way to remove drop-downs in Google Sheets, is to simply copy and paste a cell without a drop-down, into a cell that has a drop-down.

As you can see in the image below, there is a drop-down list present in cell B2. Also, cell B3 does NOT have a drop-down in it. Cell B3 is copied (note the dotted line around the cell border).

After pasting cell B3 into cell B2, the drop-down is removed from cell B2.

Create a drop-down from another sheet

If you want, you can create a drop-down list where the drop-down and the criteria list are on different sheets.

In the image below you can see that the criteria list / the list of items to use for the drop-down, is on it's own tab, in column A. For this example, this tab with the list will be named "List".

Follow the same normal process of creating a drop-down like in the previous examples. Select the cell / range of cells where you want to create your drop-down list(s), then click "Data", then click "Data validation", then choose "List from a range". But in this case, enter (or select) a criteria range that refers to column A on the tab named "List". As you can see in the image below, the cell range is: List!A2:A5

Click "Save".

The cell address / range above refers to cells A2 through A5, from the tab named "List". The exclamation point that appears after the word "List" is used to specify that we are referring to a tab name. In other words, to refer to a range on another sheet, type the name of the tab, then type an exclamation point, and then type the address for the range of cells containing the list for your drop-down.

Note that in the image below, the "Cell range" and the criteria range are referring to different tabs. The drop-down will be on one sheet, and the list of items will be on a different sheet named "List"

As you can see in the image below, after applying the data validation settings shown above, a drop-down list was created on one tab, where the drop-down refers to a list on a different tab.

This content was originally created by Corey Bustos / SpreadsheetClass.com

Color cells based on drop-down selection (Conditional Formatting)

If you want, you can apply automatic color coding to your drop-down lists, where the color of the cell will be based on the drop-down selection. This is done by using "Conditional Formatting".

In the image below, we have a sheet with drop-down lists in it. Column A shows a list of tasks, and column B has drop-downs that have already been filled in, which show the status of each task (Not Started, In Progress, or Complete).

After your drop-downs are created, select the range of cells that contain your drop-downs, then click "Format" on the top toolbar, and then click "Conditional formatting".

Create a conditional format rule, as shown below. For this example, we are formatting the cells with a light red background if the text in the cell says "Not Started".

Create additional rules for each item in your list, as shown below. In this example we are making the cell background yellow if the text in the cell says "In Progress", and we are making the cell background green if the text in the cell says "Complete".

Below you can see what the conditional formatting does, after applying the rules mentioned above. Google Sheets automatically colors the cells in column B, based on the selection of the drop-down lists.

Pop Quiz: Test your knowledge

Answer the questions below about drop-down lists in Google Sheets, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.