HomeGoogle DocsSpreadsheetMulti-Row Dynamic Dependent Drop-Down List in Google Sheets

Multi-Row Dynamic Dependent Drop-Down List in Google Sheets

Published on

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.

A Simple Drop-Down List in Google Sheets

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.

Drop-Down List From a Range
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.

Drop-Down List and Dependent Drop-Down List - Difference

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.

Two Authors and Books - Table

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.

Named Range for Dependent Drop-Down in Google Sheets

Similarly above, add another name Leo_Tolstoy for the range D3:D10. So we have two named ranges now!

Added Names (Authors)

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.

Dynamic Dependent Drop-Down in Single Row - Formula

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.

Finished Dependent Drop-Down in Google Sheets

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.

Dynamic Multi Row Dependent Drop-Down List - Sample Data

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.

Step 2: Named Ranges

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.

Step 3 - Adding Multi-Row Dynamic Dependent Drop-Down List

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.

Multi-Row Multi Column Dynamic Dependent Drop-Down List - Final

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!

drop_down_sample

Please feel free to make a copy of this sheet, where I’ve done my above experiments.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

127 COMMENTS

  1. 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,

          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.

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

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

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

  5. 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?

  6. 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?

Comments are closed.