Drop-downs are one of the best features of Google Sheets, allowing you to sort your data easily. Creating a drop-down is super easy; however, creating one from another sheet can be a little tricky.
In this article, we will explore all the methods for creating a drop-down from another sheet. Whether you are a beginner or just looking for more advanced techniques, you will find it all here.
➤ The easiest way to create a drop-down list from another sheet is by using a direct range reference.
➤ Go to the Sheet where you want to create your drop-down list and select the cells.
➤ Go to Data > Data Validation from the toolbar.
➤ Add rule > Dropdown (from a range) > Select range from the master data sheet.
➤ Hit OK, and a drop-down list from another sheet will be added.
Creating a Drop-down List with a Direct Range Reference
Creating a drop-down list with a direct range reference is the quickest way to use data from another sheet. Let’s say we have one sheet containing all the data: ‘Stocks’, ‘Supplier’.
Now, in another sheet in the same workbook, we want to create a drop-down list for ‘Supplier‘. Here’s how you can do it:
Steps:
➤ Select the cells where you want the drop-down list to appear. As I want to add the drop-down in the E column, I have selected those cells.
➤ Go to Data > Data Validation.
➤ Click on ‘Add Rule’. Under the Criteria, select List from a range.
➤ Select the small icon on the right to open the range dial box.
➤ In the box, type the sheet name and cell numbers where your data is stored (e.g., data!B2:B13), or you can go to the master sheet and select the cells. I have selected the cells from my ‘Data’ sheet.
➤ Now, click OK, and a drop-down will be added to the E column of your sheet. You can select the Supplier name from the drop-down.
Create a Drop-down List Using a Named Range
You can create a drop-down list from another sheet by naming your data range. This is a very straightforward way. Works well with large datasets. For this method, we will create a drop-down for the ‘Stocks’. We have the data stored in the master Data sheet. Here’s how you can do it:
Steps:
➤ Open the sheet where your data is stored. My data is stored in the ‘Data’ sheet, so I will open that.
➤ Highlight the cells that contain the values you want in your drop-down list. I want to create a drop-down list containing the Stock data, so I have selected it.
➤ Go to Data > Named ranges.
➤ Give the ranges a name. I have named it StockName.
➤ Now go back to the sheet you want to create a drop-down.
➤ Go to Data > Data Validation.
➤ Under Criteria, choose Dropdown from a range.
➤ In the dial box, type =StockName.
➤ Click ‘Done‘, and a dropdown will be added to the column.
Create a Dependent Drop-down List
Dependent drop-down lists are dynamic and come in handy when dealing with a large amount of data. In a dependent drop-down, one drop-down list depends on the selection in another cell’s drop-down list.
Here, we will create a dependent drop-down list for the Category column that depends on the selection in the Item Names drop-down list. Here’s how you can do it:
Steps:
➤ First, you need to sort your master datasheet appropriately. Ensure that all categories relevant to your specific Item Name are listed. Here, in the Data sheet, I have stored my Item Names in the C column and Category names for each Item Name in the following columns (D, E, F, G).
Note:
You must name each category column with the exact Item Name.
➤ Name the items and each category. First, name the ‘Item Name’ ranges. Go to Data > Named ranges > select ranges (C2:C5).
➤ Now go to the sheet where you want to create the actual drop-down. First, create a drop-down for the Item Names, following the method we discussed in the first section of this article.
➤ Go to the master data sheet and in any of the empty cells, enter this formula
=INDEX(D2:G3,,MATCH(‘Dependent drop-down’!A2,D1:G1,0))
This Formula separates the category names for each Item Name. You need to apply this formula for each Item Name by changing the names from the drop-down you have created in the Item Names.
In this formula, the A2 cell contains ‘Mouse’, so it has separated the categories for ‘Mouse’.
➤ Now, apply this formula again in another empty cell of the Data sheet to separate other items’ category names.
➤ Go to the sheet you have created, select another Item Name. Here we have chosen ‘Chair’.
➤ Now apply this formula
=INDEX(D2:G3,,MATCH(‘Dependent drop-down’!A3,D1:G1,0)).
➤ After applying this formula, the categories for Chair have been separated. Now, you can return to the sheet where you are creating the drop-down and simply select ‘Drop-down from range’ and apply the J3-J4 range from the Data sheet.
Following this formula, separate category names for each Item Name, and then apply a drop-down in the category column one by one. After doing so, you will be all done with your dependent drop-down list.
Practice Resources
Get our practice workbook or necessary files that we have worked with to prepare this article.
Create a Drop Down List from Another Sheet in Google Sheets [Make a Copy]
Frequently Asked Questions (FAQs)
What happens if I delete or move the source data?
If you delete or move the range you’re referencing, the drop-down list will break. It’s always good to double-check your ranges or use named ranges for easier reference.
Can the drop-down list appear on multiple cells?
Yes, you can select multiple cells and apply data validation rules simultaneously, making the same drop-down list available across all selected cells.
Can I arrange the drop-down list to display values in a custom order?
You can sort the list either manually or by using the SORT function in a helper column to display your drop-down items in any order you prefer.
Concluding Words
Creating a drop-down list from another sheet is a simple yet powerful way to simplify data entry and your sheets. Whether you are working with a vast dataset or a smaller one, these methods will be helpful every time.
By mastering the mentioned techniques, you can be a Google Sheets wizard and enhance your data management capabilities. Feel free to try them all and ask any questions you may have in the comments. We are already eager to hear from you.
