We can create multi row dynamic dependent drop down list in Google Sheets that without Scripts. I am just going to use only built-in Google Sheets functions to create a multi row dynamic dependent drop down list.
Why Dynamic Dependent Drop Down Lists Are Useful?
I will explain you why a dynamic dependent drop down list in Google Sheets is a must.
Imagine you are running a book store. Here you can create a drop down list that containing all the authors’ name and their book titles. When you get a bulk order enquiry, normally from educational institutions, you can send this drop down list to the customer via mail. So that they can easily the understand available book titles with you. Also with few clicks, they can select the author as well as book titles from drop down and send back to you. 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 a limited spreadsheet exposure can easily create a simple drop down list, not dynamic. How?
I think I should touch this part first before going to our tutorial – Dynamic Dependent Drop Down List in Google Sheets. This 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 do as follows.
In Cell A2, you can see a drop down list. This drop down list allows you to pick any item from the range in C2:C8 from with in A2.
Here active Cell is Cell A2. Then Go to the menu Data and click Data Validation. Set the rules as below.
Our earlier tutorial Restrict People from Entering Invalid Data on Google Doc Spreadsheet also shed more light in to data validation.
Now let us move to more complex forms of drop down list.
B] How to Create a Dependent Drop Down List in Google Sheets
What is the Term Depended 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.
In the above screenshot you can see a drop down list in Cell A1 and its dependent in Cell B1. When you select “Leo Tolstoy” in Cell A1, you can able to select his books in Cell B. When you pick another author from the list, the same author’s book should be available for selection in Cell B.
Now let us learn how to create a dependent drop down list in Google Sheets.
There are two authors name here in data range C2:D2. This range we can use to create a drop down list. Then you can 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 data in Cell C2:D2 for the drop down as below. This is same as the simple drop down list detailed under the title A.
It’s like this. Make sure that Cell A2 is the active cell. Then go to Data > Data Validation.
Criteria is “List from Range” and Criteria Range is C2:D2. That’s all. We have now created a drop down list in Cell A2 as below that contain two of the authors’ names.
In Cell B2, we are going to create a dependent drop down. It’s some times called as 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 Named Ranges feature, we can name a range like C2:C9 as something like sales, total, like any name and use the same in formula instead of the range.
Named Range is not a must here. Then why we are using it?
The main reason, we can use Named Ranges in Formulas instead of range. 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 menu Data > Named Ranges and click “Add Range”. Here I’ve given the name Helen_Keller as Named Range name for the range C3:C9. I’ve used underscore as Named Ranges won’t accept spaces.
Similarly above, add another Name to Range as Leo_Tolstoy for the range D3:D10. So we have two named ranges.
Formula Part in Google Sheets Dynamic Drop Down List
We have already a drop down list in Cell A2. Now we should write a formula connecting the drop down and the list. In Cell E2 we can write the formula.
I am just doing a logical test with Google Sheets IF logical function here. The formula compare the value in Cell A2 with C2. If matching, the formula would populate the value from the range C3:C9 (Helen_Keller), otherwise it would populate the range D3:D10 (Leo_Tolstoy).
We can’t independently use Named Range in logical test. You should use it with Indirect Function as above.
What is the benefit of using Indirect and named range combo here. Actually, it’s equal to the below formula.
With Named Range and Indirect combo, we can just avoid the ArrayFormula. Also when the number of lists are large, using an Indirect and Named Range combo can make your formula more cleaner.
Now when you select any item from drop down in A2, accordingly the values populate in cell E2:E, where we applied the formula. Now we can move to the final step, that is creating the dependent drop down list.
To do that, go to cell B2, then go to the menu Data > Data Validation.
Set the criteria range E2:E10. Done! 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 just need to modify the formula for this. But again I’m repeating the whole process with more authors and books.
C] How to Create a Multi Row Dynamic Dependent Drop Down List in Google Sheets [Advanced]
Now let us create a multi row dynamic drop down list. As said above, we are following the same steps under title B. But few more additional steps are required, that I will explain as and when required.
In Cell A1, first we should create a drop down list with author’s name. In order to do this;
Go to the Menu Data > Validation. Select the criteria range C1:F1, that is the author’s names.
Here we have a list of authors as drop down in Cell A1. From this, we can select any of the four authors. Now 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.
Name Ranges: See the set of named ranges for this purpose. If you have any doubt creating Named Ranges, just go back to the steps under Title B.
Now the Formula Part in Multi Row Dynamic Dependent Drop Down List in Google Sheets
Apply the below formula in Cell G1.
Now in B1 we can create a dynamic dependent drop down list. Again I am telling you all the steps are already detailed under Title B above.
Go to B1 and then select the Data menu Data Validation. Set the criteria range G1:G15.
Now you can select any author from under the drop down list in Cell A1. Then you can select the corresponding author’s book from Cell B1.
Up to here the steps are similar under Title B. Now we want the same list in multi row. I mean we are creating Multi Row Dynamic Dependent Drop Down List in Google Sheets.
Here just copy and paste the drop down lists in A1 and B1 to adjoining cells down won’t work.
We should modify the above formula in Cell G1 as below. The coloured part are the addition to the above formula.
Now go to cell B1 and modify the data validation range from G1:G15 to G1:T1.
Now copy and paste the lists in Cell A1 as well as Cell B1 to down wards as per your requirement. Then, in cell B2, B3, B4 etc. change the data validation range as G2:T2, G3:T3, G4:T4 respectively. This’s because while copy and paste a data validation list, there is no option in Google Sheets to change the range automatically.
We have successfully crated our first ever Multi Row Dynamic Dependent Drop Down List in Google Sheets. Hope you enjoy!
Feel free to make a copy of this sheet, where I’ve made my above experiments.