How do I make an automatic drop down list in Google Sheets?

We can create a 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 to you, why a dynamic dependent drop-down list in Google Sheets is a must.

Imagine you are running a bookstore. Here you can create a drop-down list that containing all the authors name and their book titles.

When you get a bulk order inquiry, normally from educational institutions, you can send this drop-down list to the customer via email. So that they can easily understand the available book titles with you.

Also with few clicks, they can select the author as well as book titles from the drop-down and send them 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 within A2.

Steps:

Here the active Cell is Cell A2 in Sheet2. Go to the menu Data and click Data Validation. Set the rules as below.

Our earlier tutorialRestrict People from Entering Invalid Data on Google Doc Spreadsheetalso 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

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 B1.

When you pick another author from the list, the same authors book should be available for selection in Cell B1.

Now let us learn how to create a dependent drop-down list in Google Sheets.

There arethe names of two authors in the range C2: D2. This range we can use 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 data in Cell C2: D2 for the drop-down as below. This is the same as the simple drop-down list detailed under title A.

Its like this. Make sure that Cell A2 is the active cell. Then go to Data > Data Validation.

The Criteria is List from Range and Criteria Range is C2: D2. Thats all. We have now created a drop-down list in Cell A2 as below that contains two of the authors names.

In Cell B2, we are going to create a dependent drop down. Its 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 like C2: C9 as something like sales, total, like any name and use the same in formulas instead of the range.

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 we are using it?

The main reason, we can use Named Ranges in formulas instead of a range. By doing so the formula becomes more readable in the future.

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 Ive given the name Helen_Keller as Named Range name for the range C3: C9. Ive used underscore as Named Ranges wont 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.

Formula:

=if(A2=C2,indirect("Helen_Keller"),Indirect("Leo_Tolstoy"))

I am just doing a logical test with Google Sheets IF logical function here.

The formula compares 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 cant independently use Named Range in the logical test. You should use it with Indirect Function as above.

What is the benefit of using the Indirect and named range combo here? Actually, its equal to the below formula.

=ArrayFormula(if(A2=C2,C3:C9,D3:D9))

With Named Range and Indirect combo, we can just avoid the ArrayFormula. Also when the number of lists is large, using an Indirect and Named Range combo can make your formula cleaner.

Now when you select an item from the 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, which 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 want to modify the formula for this. But again Im 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, which I will explain as and when required.

In Cell A1, first, we should create a drop-down list with the authors name. In order to do this;

Go to the Menu Data > Validation. Select the criteria range as C1: F1, which is the authors names.

Here we have a list of authors as a 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.

Lets do that part.

Named Ranges: See the set of named ranges for this purpose. If you have any doubt about 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.

=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 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 authors book from Cell B1.

Up to here, the steps are similar to that provided under Title B. Now we want the same list in a multi-row.

I mean we are creating a Multi-Row Dynamic Dependent Drop Down List in Google Sheets.

Here just copying and pasting the drop-down lists in A1 and B1 to adjoining cells below wont work.

We should modify the above formula in Cell G1 as below.

=ArrayFormula(if(len(A1:A),transpose(ArrayFormula(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")))))),""))

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:

Make sure that, in the data validation, you use absolute reference in A1 and relative reference in B1. To do that;

Click cell A1, go to Data > Data validation, and make sure that the range is =$C$1:$F$1 not =C1:F1.

Then click cell B1 and go to Data > Data validation and make sure that the range is =G1:T1 not =$G$1:$T$1

Now copy and paste the lists in Cell A1 as well as Cell B1 downwards as per your requirement.

Update on 02-02-2021:The below step is not required.

After that, in cell B2, B3, B4, etc. change the data validation range as G2: T2, G3: T3, G4: T4 respectively. Thiss because while copy and pastes a data validation list, there is no option in Google Sheets to change the range automatically.

Thats all.

Conclusion

We have successfully created our first-ever Multi-Row Dynamic Dependent Drop Down List in Google Sheets. Hope you enjoy it!

https://docs.google.com/spreadsheets/d/1xe15-OBAe183XlJTh_mPwo67CEeQXaCcKY3UloXA2oo/edit?usp=sharing

Feel free to make a copy of this sheet, where Ive made my above experiments.