We can create a multi-row dynamic dependent drop-down list in Google Sheets without using Google Apps Script. This tutorial explains how.
I will only use built-in Google Sheets functions to create a multi-row dynamic dependent drop-down list.
Why Dynamic Dependent Drop-Down Lists Are Useful?
Let me explain why a dynamic dependent drop-down list in Google Sheets is a must-try.
Imagine you are running a bookstore. You can create two drop-down lists, one containing all the author names and the other their book titles in a Sheet. Copy them in multiple rows.
When you get inquiries, for example, from educational institutions, you can send them the Sheet via email.
They can select the authors and book titles from the drop-downs and send the file back to you (of course, you can use Google Forms for the same purpose).
There are plenty of such situations where you can use dynamic dependent drop-down lists.
A. Create a Simple Drop-Down List in Google Sheets
Anyone with limited spreadsheet exposure can easily create a simple drop-down list, not dynamic. How?
I must explain this part before starting the tutorial on creating a dynamic dependent drop-down list in Google Sheets because it is the essence of the tutorial.
Your first drop-down menu in Google Sheets is just a click away. To create a simple drop-down list, please do as follows.
In cell A2, you can see a drop-down list (menu). This drop-down menu allows you to pick any item from the range in C2:C8 from within cell A2.
Steps:
Here the active cell is A2 in “Sheet2.” 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, i.e., Sheet2!C2:C8, and Done.
Our earlier tutorial, Restrict People from Entering Invalid Data on Google Doc Spreadsheet, also shed more light on data validation.
Recommended Reading: The Best Data Validation Examples in Google Sheets.
Now let us move to more complex forms of the drop-down list.
B. How to Create a Dependent Drop-Down List in Google Sheets
To create a dependent drop-down, needless to say, first, there should be a drop-down list. It works like this.
You can see the drop-down list in cell A1 and its dependent in cell B1 in the above screenshot.
You select “Leo Tolstoy” in cell A1. Then all his books will be available to pick in cell B1.
When you pick another author from the list, the list of books in cell B1 should change accordingly.
Now let us learn how to create a dependent drop-down list in Google Sheets.
There are the names of two authors in the range C2:D2. We can use that range to create a drop-down list.
See the list of their books in the range C3:D10 that we can use to create a dependent drop-down list.
We can use the values in cell C2:D2 for the drop-down in cell A2 (Insert > Drop-down > Criteria > Drop-down (from a range) > Sheet2!C2:D2).
In cell B2, we are going to create a dependent drop-down. It’s sometimes called a Dynamic Dependent Drop Down List in Google Sheets.
Here we are using Google Sheets Named Ranges. What is the purpose of Named Ranges in Google Sheets?
With the Named Ranges feature, we can name a range, e.g., C3:C9, to something like sales, total, like any name, and use the same in formulas instead of C3:C9.
Similar: A Drop-down Menu in Google Sheets to View Content from Any Sheets in the Current Sheet
Named Range Is Not a Must Here. Then Why Are We Using It?
The main reason is we can use Named Ranges in formulas instead of a range reference.
By doing so, we can make the formulas reader-friendly.
Here we have two authors in the drop-down in cell A2. So we require two named ranges pointing to their book titles.
Create the first named range as below.
Go to the Data menu > Named Ranges and select “Add Range.”
I’ve entered Helen_Keller as the name for the range C3:C9. I’ve used underscore as Named Ranges won’t accept spaces.
Similarly above, add another name Leo_Tolstoy for the range D3:D10. So we have two named ranges now!
Formula Part in Google Sheets Dynamic Drop-Down List
We already have a drop-down list in cell A2 (author names).
Now we should write a formula connecting the selected author in it and their book titles. We will write the required formula in cell E2.
Formula:
=if(A2=C2,indirect("Helen_Keller"),Indirect("Leo_Tolstoy"))
I am doing a logical test with Google Sheets IF logical function here.
The formula tests the value in cell A2 with C2.
If it matches, the formula will populate the values from the range C3:C9 (Helen_Keller). If not, it will populate the values from the range D3:D10 (Leo_Tolstoy).
The Named Range requires the Indirect Function to work.
What is the benefit of using the Indirect and Named Range combo here?
It’s equal to the below formula.
=ArrayFormula(if(A2=C2,C3:C9,D3:D9))
With Named Range and Indirect combo, we can avoid using the ArrayFormula. Also, if there are more values to test in the logical part, using an Indirect and Named Range combo can make your formula cleaner.
When you select an item from the drop-down in cell A2, corresponding values will populate in cell E2:E.
Now we can move to the final step, i.e., creating the dependent drop-down list.
To do that, go to the menu Insert > Drop-down > Criteria > Drop-down (from a range) and enter the criteria range E2:E10, and voila! Your dynamic drop-down list in Google Sheets is ready.
From this point, we can create a multi-row dynamic dependent drop-down list in Google Sheets.
We want to modify the formula for this.
C. How to Create a Multi-Row Dynamic Dependent Drop-Down List in Google Sheets
Now let us create a multi-row dynamic drop-down list.
We are following the same steps under Title B. But a few more additional steps are required, which I will explain as and when it comes.
In cell A1, first, we should create a drop-down list with the author’s name.
To do this, Go to the menu Data > Data Validation > Add rules > Criteria > Drop-down (from a range).
Select the range as C1:F1, which is the authors’ names. (You can do the same from the Insert menu Drop-down also).
Now we have a list of authors as a drop-down in cell A1. From this, we can select any of the four authors.
We need a dependent drop-down list in cell B1 to select the book related to the author in cell A1. Let’s do that part.
Please see a set of Named Ranges for this purpose. If you have doubts about creating it, please scroll up, and see the steps under Title B.
Formula Part in Multi-Row Dynamic Dependent Drop-Down List in Google Sheets
Apply the below formula in Cell G1.
=if(A1=C1,indirect("Agatha_Christie"),if(A1=D1,indirect("Sir_Arthur_Conan_Doyle"),if(A1=E1,indirect("Helen_Keller"),INDIRECT("Leo_Tolstoy"))))
Now in B1, we can create a dynamic dependent drop-down list.
Again, for your info, all the steps are already detailed under Title B above.
Go to B1 and then select the Data menu Data Validation > Add rules > Criteria > Drop-down (from a range). Set the criteria range G1:G15.
Now you can select any author from the drop-down list in cell A1 and the corresponding author’s book from cell B1.
Up to here, the steps are similar to that provided under Title B. Now we want the same list in multi-rows.
We are creating a Multi-Row Dynamic Dependent Drop Down List in Google Sheets.
Copying and pasting the drop-down lists in A1 and B1 to the cells down below won’t work.
We should modify the above formula in cell G1 as below.
=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"))))),""))
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.
Now go to cell B1 and modify the data validation range from G1:G15 to G1:T1.
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.
Now copy and paste the lists (drop-downs) in cells A1 and B1 down the columns as far as you want.
That’s all.
We have successfully created our first-ever Multi-Row Dynamic Dependent Drop Down List in Google Sheets. I hope you have enjoyed it!
Please feel free to make a copy of this sheet, where I’ve done my above experiments.
Hello,
I have successfully used the BYROW formula. However, I wish to create a filter, so I can use sort A-Z.
When I attempt this, my cells containing dropdown lists return an error message that reads, “Input must fall within the specified range.” Any tips on how to fix this? Thank you!
Hi, LG,
Sorry! I’m unable to answer without seeing a sample of your data.
Here is the link to my copy of your sample sheet spreadsheet:
— URL remove by admin.
Hi, LG,
Thanks for sharing a sample copy. I tested and found sorting re-arrange the drop-downs as per the value in another column. But the range inside drop-downs doesn’t move because of the array formula. Sorry, I don’t find a workaround.
Hey and TY!
I believe it’s a good idea to create lists by pivoting any other list of data Author Book. I mean Sheet1 C:F columns.
We can’t pivot without aggregation but it feels like we can pivot by
=TRANSPOSE(UNIQUE...
for the 1-st row and=BYCOL(***lambda(***FILTER(...
for all other rows.Hi, Max,
I am afraid you missed my BYROW formula in this tutorial.
Hi Prashanth,
How do you use “Any” as a selection in a Dynamic dropdown box to display all data? I have tried “Any,” and
*.*
neither works.Hi, Ron,
You may check this – Getting an All Selection Option in a Drop-down in Google Sheets.
Hi! I am also trying to add more “author names” (in my case, car make and model) to my sheets, but I am unable to do so.
– URL removed by Admin –
Could you kindly help advise how I can do it by changing your suggested formula? Thanks for sharing.
Hi, Esther Ng,
Insert this new formula in cell G1. I’ve updated my tutorial and also my sample Sheet.
=byrow(A1:A,lambda(r,ifna(transpose(filter(C2:H,C1:H1=r)))))
This formula doesn’t require Named Ranges.
Hi Prashanth,
Can you help me with having the dynamic dependent drop-down on the same column, i.e., A2 (author name) -> A3 (books), B2 (author name) -> B3 (books), and so on?
I read in the older comments that it wasn’t solved with this method.
Did you find a way to achieve this, or can you maybe, redirect me to a helpful formula/blog/script?
Hi, Varun,
I’ve added a new tab named “Sheet2” in my sample Sheet, where you can find the solution to your problem.
Hey,
Everything works well, but on my side, when I insert a new row (for example, above row 10) (it’s a use case that I can have in my google shared document with my team), I lose the right dependent lists in this new row and all the row below.
The data validation in the new row and the row below is offset.
How can I solve this issue?
Hi, Jérémy,
I couldn’t replicate the problem on my side.
Consider sharing a copy (filled with mockup data).
You can include the URL in your reply below.