We all know that Google Sheets is a powerful tool for organizing and analyzing data. But did you know that you can create a multi-row dynamic dependent drop-down list in Google Sheets? And the best part is, you don’t need to use Google Apps Script to do it! In this tutorial, I will show you how to create this useful feature using only built-in Google Sheets functions.
Why Dynamic Dependent Drop-Down Lists Are Useful?
Before we dive into the tutorial, let me explain why a dynamic dependent drop-down list in Google Sheets is a must-try. Imagine you’re running a bookstore and you want to keep track of all the books and their respective authors. With a dynamic dependent drop-down list, you can easily create two drop-down menus. One will contain all the author names, and the other will display the book titles based on the selected author. This way, when you get inquiries from customers or educational institutions, you can simply share the Sheet with them. They can then select the authors and book titles from the drop-down menus and send the file back to you. It’s a convenient and efficient way to manage and share data!
A. Create a Simple Drop-Down List in Google Sheets
Anyone with limited spreadsheet exposure can easily create a simple drop-down list in Google Sheets. To create a basic drop-down list, follow these steps:
- In cell A2, go to the menu Data > Data Validation or Insert > Drop-down.
- Add the rules as per the image below.
- Go to Insert > Drop-down and under criteria, select Drop-down (from a range).
- Enter the range in the field, for example,
Sheet2!C2:C8. - Click Done.
This will create a drop-down menu in cell A2, allowing you to select any item from the specified range.
B. How to Create a Dependent Drop-Down List in Google Sheets
To create a dependent drop-down list in Google Sheets, first, we need to have a drop-down list. Here’s how it works:
- In cell A1, create a drop-down list with the author’s names.
- In cell B1, create a dependent drop-down list to select the book related to the author in cell A1.
To achieve this, we can use named ranges in Google Sheets. Named ranges allow us to assign a name to a range of cells and use that name in formulas instead of cell references. Here’s how you can create a dependent drop-down list using named ranges:
- Create a named range for each author’s book titles. For example, named range “Helen_Keller” for the range C3:C9 and “Leo_Tolstoy” for the range D3:D10.
- Write a formula in cell E2 that connects the selected author in cell A2 with their book titles. The formula should be:
=if(A2=C2,indirect("Helen_Keller"),Indirect("Leo_Tolstoy")). - Select cell B2 and go to Insert > Drop-down > Criteria > Drop-down (from a range).
- Set the criteria range as E2:E10.
Now, when you select an item from the drop-down in cell A2, the corresponding values will populate in cell B2.
C. How to Create a Multi-Row Dynamic Dependent Drop-Down List in Google Sheets
Now, let’s take it a step further and create a multi-row dynamic dependent drop-down list in Google Sheets. Follow these steps:
- Create a drop-down list with the author’s names in cell A1 using the same method as in Title B.
- Create named ranges for each author’s book titles, as explained earlier.
- Apply the following formula in cell G1:
=ArrayFormula(if(len(A1:A),transpose(if(transpose(A1:A)=C1,indirect("Agatha_Christie"),if(transpose(A1:A)=D1,indirect("Sir_Arthur_Conan_Doyle"),if(transpose(A1:A)=E1,indirect("Helen_Keller"),INDIRECT("Leo_Tolstoy"))))),"")). - Select cell B1 and go to Data > Data Validation > Add rules > Criteria > Drop-down (from a range).
- Set the criteria range as G1:T1.
Congratulations! You have now created a multi-row dynamic dependent drop-down list in Google Sheets. You can copy and paste the drop-down lists in cells A1 and B1 down the columns as far as you want.
A Very Important Update on 08-Feb-2023:
You can replace the above complex formula with the below BYROW one.
=byrow(A1:A,lambda(r,ifna(transpose(filter(C2:F,C1:F1=r)))))
This formula doesn’t use INDIRECT, so the named ranges are also not required.
A Very Important Update on 02-Feb-2021:
Please make sure one thing! You are using absolute cell reference in the A1 and relative cell reference in the B1 criteria range. To do that, go to Data > Data validation, select the A1 rule, and ensure that the range is $C$1:$F$1, not C1:F1. Then select the B1 rule and ensure that the range is G1:T1, not $G$1:$T$1.
That’s it! You’ve successfully created a multi-row dynamic dependent drop-down list in Google Sheets. Enjoy exploring the endless possibilities of organizing and analyzing your data!
Please feel free to make a copy of this sheet, where I’ve done my above experiments. Crawlan.com
