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

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.

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.

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.

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!

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.

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.

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.

127 COMMENTS

LG June 5, 2023 At 9:20 pm

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!
- Prashanth June 6, 2023 At 8:50 am
  
  Hi, LG,
  
  Sorry! I’m unable to answer without seeing a sample of your data.
  - LG June 7, 2023 At 7:12 pm
    
    Here is the link to my copy of your sample sheet spreadsheet:
    
    — URL remove by admin.
    - Prashanth June 9, 2023 At 10:05 am
      
      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.
Max April 25, 2023 At 12:01 am

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.
- Prashanth April 25, 2023 At 1:57 pm
  
  Hi, Max,
  
  I am afraid you missed my BYROW formula in this tutorial.
Ron February 22, 2023 At 2:19 am

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.
- Prashanth February 25, 2023 At 3:19 pm
  
  Hi, Ron,
  
  You may check this – Getting an All Selection Option in a Drop-down in Google Sheets.
Esther Ng February 7, 2023 At 10:28 pm

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.
- Prashanth February 8, 2023 At 12:03 pm
  
  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.
Varun November 25, 2022 At 11:58 am

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?
- Prashanth November 26, 2022 At 10:27 am
  
  Hi, Varun,
  
  I’ve added a new tab named “Sheet2” in my sample Sheet, where you can find the solution to your problem.
Jérémy September 7, 2022 At 7:32 pm

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?
- Prashanth September 10, 2022 At 10:40 am
  
  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.
Raaj Pathak August 5, 2022 At 10:59 pm

Hi Prashanth,

Thank you so much for this. However, I need one help.

Look into the file. I did everything you showed for this to work.

The F2 (Sub Category) returns the correct value for E2 (Root Cause). But how do I apply the same thing to the entire sheet?

— URL removed by admin —
- Prashanth August 9, 2022 At 9:31 am
  
  Hi, Raaj Pathak,
  
  The formula should be in the last column so that it can expand.
  
  I’ve updated your Sheet with the correct formula and its placement.
Preksha August 5, 2022 At 11:34 am

Hello!

I have multiple “author names” in my worksheet, so I have replicated if(transpose(A1:A)=D1,indirect("Sir_Arthur_Conan_Doyle") for each “author” I have.

However, it gives me the correct results only for the first 4 “authors”. How do I get the data for the remaining ones?

Not sure if this has something to do with the IF formula limitation?

Thanks!
- Prashanth August 5, 2022 At 6:39 pm
  
  Hi, Preksha,
  
  I am happy to help if you can share a copy of that sheet. You can leave the URL in your comment reply.
  - Preksha August 8, 2022 At 2:11 pm
    
    Hi Prashanth,
    
    Thanks! I’ve left the URL and my notes.
    
    Hope that helps!
    - Prashanth August 9, 2022 At 4:06 pm
      
      Hi, Preksha,
      
      Your last part of the formula is INDIRECT("")))))),""))))))) which is wrong. It should be INDIRECT("Freelancers"))))))))))),""))
      
      I’ve already modified your Sheet.
Vivek Shah July 5, 2022 At 4:10 pm

Can you please share me the Apps Script?
- Prashanth July 6, 2022 At 7:29 am
  
  Hi, Vivek Shah,
  
  Please check ‘older comments’.
Andrea April 2, 2022 At 8:38 am

Thank you so much. This is exactly what I needed! You explained it so well!
Paul March 22, 2022 At 4:00 pm

Hi Prashanth,

I have a problem using the formula you posted for cell E2.

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

I always get a parse error.

Can you please help me?

=if(A2=C2,indirect("apple"),Indirect("plum"))
- Prashanth March 23, 2022 At 9:23 am
  
  Hi, Paul,
  
  Please make sure that you have created the named ranges.
  Also, the parse error might be due to the LOCALE settings.
  You can share an example sheet for me to check by leaving the URL in the ‘reply’ below.
ananda March 13, 2022 At 1:16 am

I have a question to ask. I select a value in the drop-down (say column B2), I need to populate a matching value in C2 based on the value in B2.

May be B column contains vendor and c column telephone#

How can this be done? Can you help?
- Prashanth March 13, 2022 At 10:50 am
  
  Hi, ananda,
  
  We may be able to use Vlookup. You can leave a sample Sheet’s URL below.
Josiah October 22, 2021 At 11:28 pm

I’m having issues retaining the formula in the first cell of the row so that it isn’t broken by a sort.

I think the fix is to have arrayformula in the header, but I’m not sure how to retain functionality by either including a title or having the functionality start on the next row.
- Prashanth October 24, 2021 At 9:24 am
  
  Hi,
  
  Please try this fix – How to Stop Array Formula Messing up in Sorting in Google Sheets.
Josiah October 22, 2021 At 6:20 am

Thanks for this, this is great.

One thing I’m running into issues with is if I sort the column B (it is a date in my case), the reference between the two columns is broken to different rows. Instead of A1 corresponding with B1, A1 corresponds with B3 or something after the sort. I have quite a bit of drop downs, so everything is on a different sheet. Any thoughts on how to fix that?
Eugene August 23, 2021 At 10:43 pm

Thank you!

Your chain of “if” statements seems like it could get clunky if you have hundreds of authors you could look up.
One edit that I made to your method was to avoid having to do all those “if” statements in your array formula.
I just did;

=arrayformula(transpose(indirect(substitute(A2, " ", "_"))))

The upside is that it automatically pulls the correct named range without the “if” statements.
It does have the downside that I couldn’t get the arrayformula doesn’t fill down all the way anymore.
So you have to drag and copy it down. And you’d still have to create all those named ranges.
- Prashanth August 24, 2021 At 9:49 am
  
  Hi, Eugene,
  
  I agree with you, and thanks for your feedback.
- Mathieu Meyer December 10, 2021 At 12:51 am
  
  That is so helpful!
  
  I don’t need one Array Formula. I used automatic filling so that some hidden columns always show a single array formula dependent on the adjacent cell in the row.
  
  This formula is much simpler.
  
  Thank you also, Prashanth, for your help, great as always!
  - Andrea April 2, 2022 At 9:05 am
    
    Can you explain this, please? I am trying to understand how to do it for my finance tracker.
    - Prashanth April 3, 2022 At 7:37 am
      
      Hi, Andrea,
      
      He seems to use the ‘Auto Fill’ suggestion to fill column G, instead of using an auto-expanding array formula in cell G1.
Celestina August 18, 2021 At 9:43 pm

Hi,
It is the best help I have found so far. But I’m having problems when adding columns in between.

I see your sample in the tutorial having no problems with it, but mine won’t have the correct dependent dropdown and array of options.

Did you still use the script for this? I hope you can help me soon. Thanks!

Here’s an editable copy for your reference.

— link removed by admin —
- Prashanth August 19, 2021 At 10:02 am
  
  Hi, Celestina,
  
  Nope! I have not used any Apps Script.
  In my example sheet, I have used an array formula in Sheet1!G1.
  In the place of that, you have used a non-array (drag-down) formula in MAIN!D3, which I have modified.
  In addition to that, I have modified your named rages. You were using a whole column range, which may cause issues. I have used a small limited range instead.
  Please, check your sheet.
  - Celestina August 19, 2021 At 4:18 pm
    
    Oh, I see the difference.
    
    I guess I didn’t exactly copy and modified the formula correctly.
    
    May I ask, how exactly did I make it a non-array formula? Is it because I put (A3) instead of (A:A3)?
    
    Also, I made the ranges with 20 columns only. The whole column range did cause some errors.
    - Prashanth August 20, 2021 At 9:28 am
      
      Hi, Celestina,
      
      You can find two formulas below the sub-title “Now the Formula Part in Multi-Row Dynamic Dependent Drop Down List In Google Sheets.”
      To know the array/non-array difference, please compare them.
Timothy Higgs July 3, 2021 At 4:52 pm

How can I add extra columns with more data?

When I try to extend out your formula I start getting “Array arguments to IF are of different size.” errors
- Daniel Karp November 2, 2021 At 11:02 pm
  
  I am receiving the same issue, otherwise working perfectly. Any solutions?
Ashley June 9, 2021 At 6:04 pm

Hey, I am currently using a separate tab to keep my transposed rows. I need to be able to delete, move, filter, and insert rows into my original sheet (with the dropdowns). Is this possible? Currently, it causes errors. Any help appreciated.
laurent March 18, 2021 At 3:58 am

Hello,

What can I do if I want to have in column A a “company list” and in column B “the list of contact”s who are attached to the company?

Knowing that new contacts can be attached to each company.

Thx
- Prashanth March 18, 2021 At 9:59 am
  
  Hi, laurent,
  
  Replace C1:F1 with company names (this will create the company list). In my example, this cell range contains author names.
  
  Replace C2:F with corresponding contacts. In my example, this cell range contains book titles.
henry March 15, 2021 At 9:15 pm

Thank you so much.
Sunil February 17, 2021 At 5:02 pm

Thanks, Prashanth for the update. Will look forward to the new feature reflecting soon.

Regards,
Sunil
Martin February 17, 2021 At 2:53 pm

Hi. Sorry to interrupt you. Would you please explain the following two things to me?

len(A1:A),
transpose(A1:A)=C1

What does A1:A mean?
- Prashanth February 18, 2021 At 8:41 am
  
  Hi, Martin,
  
  A1:A is column A which contains the drop-downs. len(A1:A) means non-blank cells in the range A1:A.
  
  The function TRANSPOSE used to change the column orientation (vertical to horizontal).
  - Jaishree August 17, 2021 At 7:00 pm
    
    The question here – If I add a new row to the sheet, here my formulae is going for a toss – how to avoid that?
    - Prashanth August 18, 2021 At 4:44 am
      
      Hi, Jaishree,
      
      I couldn’t reproduce that issue in the example sheet that I included within this tutorial.
Sunil February 17, 2021 At 1:57 am

Hello Prashant,

I tried everything as you mentioned post the update but when it comes to mentioning ='Sheet1'!$H$16:$K$16 in the Data range under Data validation (C1:F1 as per your example), when I click save, there is a red error box appears and it says “Please enter a valid range”.

Could you please help here because only the first cell works (A1 for you and D3 for me) but when I copy and paste further below, nothing shows up in (B2, your example)?

Could you please help?

Thanks,
Sunil
- Sunil February 17, 2021 At 2:09 am
  
  In your example of B1 > Data > Data Validation > enter data range > here the system is not accepting when I type ='Sheet1'!G1:T1. It says “Please enter a valid range”.
  
  Please help.
  
  Thanks,
  Sunil
  - Prashanth February 17, 2021 At 8:47 am
    
    Hi, Sunil,
    
    Please make sure you spelled the sheet name correctly.
    
    Also, you can check my example sheet for the same.
    - Sunil February 17, 2021 At 1:35 pm
      
      Hi Prashant,
      
      Yes, I’ve spelled it correctly, and also checked your example sheet, which very clearly has the data range starting from = and also accepts the $ value prefixed to the cells. When I do the same, the error message is “Please enter a valid range”.
      
      I’ve spent hours just re-entering the data validation range in multiple options, but it never worked.
      
      Please do help me.
      
      Thanks,
      Sunil
      - Prashanth February 17, 2021 At 2:07 pm
        
        Hi, Sunil,
        
        It’s actually a new Google Sheets feature. I think it’s not yet rolled out fully. So please wait a few more days.
Rachel February 8, 2021 At 9:17 am

Hi,

I have managed to do all the formulas. My only problem is copying B1 downwards. The list range does not automatically copy. I have about 1000 rows.
- Prashanth February 8, 2021 At 10:56 am
  
  Hi, Rachel,
  
  Earlier there was the issue. Now the drop-down supports relative reference.
  
  In B1, the data validation “List from a range” must be =G1:T1, not =$G$1:$T$1.
  
  When you copy down you will see the copied drop-down flagged. Just delete the values by selecting B2:B1000.
  
  Related – Relative Reference in Drop-Down Menu in Google Sheets.
Raju January 12, 2021 At 2:18 am

Hello Prashanth,

Following is the link to the sheet.

… link copied and then removed by the admin …

Would like your help in resolving this issue I’m facing.

I have managed to get data validated in the same row depending on the selection of cell A1. But I would like to get it vertically.

Eg.: Cell B2 has (rooms)
Need B3 to give dynamic data validation depending on the room type selected in B2.
- Prashanth January 15, 2021 At 9:20 am
  
  Hi, Raju,
  
  I have gone through your sheet to understand your requirement. You want the drop-down and the dependent drop-down in the same column.
  
  My solution won’t be able to fulfill your requirement as the formula in G1 populates the dependent drop-down values (B1:B drop-down) based on the values in column A (A1:A drop-down).
OM Kumar Chaurasiya January 8, 2021 At 2:33 pm

When I drag the validation column to below, dependent data validation not dynamically changed. There is needed one by one data validation.

I need an entire column, could you please help me.
- Prashanth January 8, 2021 At 2:54 pm
  
  Hi, OM Kumar Chaurasiya,
  
  You may require to use Apps Script. Please go thru’ the comments to find the relevant link.
- Prashanth February 2, 2021 At 12:01 pm
  
  Hi, OM Kumar Chaurasiya,
  
  It changes now. Thanks, Google for the update.
  
  You may please check my tutorial once again to see the update.
Manorma RANA July 16, 2020 At 10:55 am

Hi Prashanth,

I have used your formula for 10 column data. I have added values accordingly but the formula works only for 4 columns.

I’m getting the error “Wrong number of arguments to IF. Expected between 2 and 3 arguments, but received 4 arguments.”

Can you help?
- Prashanth July 18, 2020 At 9:41 am
  
  Sheet URL (with edit access) please.
Tricia Curran May 15, 2020 At 4:04 am

Thanks for this! The only issue I’m currently having is that I get #N/As appearing in my dropdown list because the lists are shorter. Is there a way to avoid this? Thanks in advance.
- Prashanth May 15, 2020 At 10:40 am
  
  Use IFNA() to remove the N/A errors from the data validation range. I haven’t seen that issue in my test.
Onyx May 8, 2019 At 4:14 am

Thanks for this! It was quite helpful, and while a little clunky, it allowed me to do exactly what I needed to do. Nevermind manually changing 200 cells…it is still much better than the alternatives.
- Prashanth February 2, 2021 At 11:59 am
  
  Hi, Onyx,
  
  Now, no need to manually change the rows. I have updated the tutorial based on the lastest Google Sheets program changes.
Bo April 20, 2019 At 12:22 am

How can I add a third column? A dependent list for the first dependent list?
Kelly April 19, 2019 At 5:31 pm

Thank you so much, I used your example and was able to create a basic work order for our flooring business. One question that I have, how can I now connect the options to a price in a third column? For example Author > Title > Price. I would love to do that but my understanding of sheets and excel is limited.
- Prashanth April 19, 2019 At 10:02 pm
  
  Hi, Kelly,
  
  You can maintain a price list in another tab and use Vlookup. If you can share a copy (just demo data) of your Sheet, I may be able to help you.
  
  Best,
j April 15, 2019 At 12:45 am

My multiple dropdowns work, as long as I do not sort. If I want to sort alphabetically then the data validation gets messed up. Suggestions?
- Prashanth April 15, 2019 At 11:48 am
  
  Hi,
  
  Open a new tab and use the SORT formula.
  
  =sort(Sheet1!A1:B,1,true,2,true)
  
  If you want you can filter the author too.
  
  =filter(Sheet1!A1:B,Sheet1!A1:A="Helen Keller")
  
  Best,
  
  Prashanth KV
Chris March 15, 2019 At 2:59 am

“This’s because while copy and pastes a data validation list, there is no option in Google Sheets to change the range automatically.”

I’m working with more than 3k+ data points. It’s literally faster to just have my team type in the data for themselves. If there is an actual way of creating dependent dropdowns I would love to hear it.
- Prashanth March 15, 2019 At 4:07 pm
  
  Hi, Chris,
  
  Sorry for the inconvenience.
  
  If you have several rows of data, you must depend on a script.
- Prashanth February 2, 2021 At 11:58 am
  
  Hi, Chris,
  
  Here is a good news. Google has added the capability. Based on that I have updated my tutorial. Please go through it again.
amaury March 7, 2019 At 8:45 pm

Hello, Thanks for that.

Do you have it with 4 dependent dropdown column?

Regards,

Amaury
- Prashanth March 8, 2019 At 11:11 am
  
  Hi, Amaury,
  
  You can do that by following my tutorial. I don’t have a template for that now.
James March 6, 2019 At 7:34 pm

Hello Prashanth,

I did a similar project 3 years ago on March 2, 2016….

https://docs.google.com/spreadsheets/d/1q9ZTWGiJvDgk6RTuWffXbtAGGGf7sBZsIo2GlPaMYwY/copy

TC James/mreighties 🙂
- Prashanth March 7, 2019 At 9:40 am
  
  Hi, James,
  
  Welcome!
  
  It’s a different approach and good too. It also serves the purpose.
Aziz February 25, 2019 At 7:49 pm

I have a problem, i want to make a multiple dropdown list but it’s don’t work.

Can you help me ?
Ben November 16, 2018 At 9:39 am

Hi Prashanth:

Thanks for sharing this awesome sheets and it’s really helpful.

I have one question. Is there any way to auto-generate Column B’s Data Validation items?

Ex:

B1 -> Data Validation mapping to G1:T1

B2 -> Data Validation mapping to G2:T2

B3 …
- Prashanth November 16, 2018 At 9:51 am
  
  Hi, Ben,
  
  I am glad that you find this drop-down list useful.
  
  “Is there any way to auto-generate Column B’s Data Validation items?”
  
  Sadly there is no way to do that automatically. This is a drawback in Google Sheets.
- David Neff September 5, 2019 At 7:14 am
  
  Thanks for the tutorial Prashanth… and the comment Ben, I have been beating my head against the wall for two days trying to find a way around this.
  
  So, as the bruising and concussion fade, tell me it isn’t true and that I can pull down and fill a thousand rows and not have to manually change the validation coordinates.
  
  Can’t use a variable in the data range? tell me I’m wrong!!!
  - Prashanth February 2, 2021 At 11:55 am
    
    Hi, David Neff,
    
    Now you can! Please read the tutorial for the update.
- Prashanth February 2, 2021 At 11:54 am
  
  Hi, Ben,
  
  Yes! It’s now possible. Please check the tutorial.
neil November 4, 2018 At 3:25 am

Hi, how can I increase the number of columns ( for example, in reference to your sheet, let’s say I want to add more writers? I tried adding another if function but it gives me an error message saying IF ONLY TAKES 3 ARGUEMENTS
- Prashanth November 4, 2018 At 9:16 am
  
  Hi, Neil,
  
  As per my example, do as follows.
  
  Insert a new column after column F. In that (now column G) in cell G1, type the new author’s name as the field label, and below that enter the book titles.
  
  Here is the formula in Cell F1 (earlier it was in cell G1). This time I have removed the Named Ranges and used the actual cell references. So that you can clearly read the formula.
  
  =ArrayFormula(if(len(A1:A), transpose(ArrayFormula(if(transpose(A1:A)=C1,C2:C15, if(transpose(A1:A)=D1,D2:D15,if(transpose(A1:A)=E1,E2:E15, if(transpose(A1:A)=F1,F2:F15,G2:G15)))))),""))
  
  One more thing to do! Change the data validation in cell A1. To do that click cell A1 and go to the Data menu Data Validation.
  
  Change the criteria range to C1: G1 to include the new author.
  
  Hope this may help.
  
  Cheers!
Prashanth August 30, 2018 At 11:42 am

Hi, Kelum Erandika,

As you said, if we can use Indirect in data validation like the Excel does, creating a depending drop-down list would be just kids play. Unfortunately, it’s not.

But Google is steadily improving their platform. Lots of features are adding to it nowadays. So we can hopefully expect such an Indirect and Data validation compatibility in the future.

This site is not affiliated to Google Sheets. So your question is not relevant that whether I have informed this feature request to Google (I am not denying the fact that I am Google TC in Google Sheets)

Hope this helps.
Kelum Erandika August 30, 2018 At 2:43 am

Dear Mr. Prashanth

Thanks a lot for your tutorial. It’s amazing. So I need to ask some question to you?

1) Why still Google is failed to develop their, Google Sheets to change the range automatically?.

2) Why still Google is failed to allow =indirect() in the data validation (like excel does).?

3) Have you already informed to the Google, the above two topics as their improvement?

And the other thing. Can you check my google sheet? I can share it with you. How can I share my doc for You? Please tell me.

Kelum Erandika
- Prashanth February 2, 2021 At 11:52 am
  
  Hi, Kelum Erandika,
  
  The good news is that now you just need to drag the drop-down to change the range. Updated the tutorial as well as in my shared sheet.
Juan Dela Cruz August 7, 2018 At 9:02 am

Is there a way to auto delete data in column B upon deleting data on column A?
- Nicolas August 15, 2018 At 11:50 pm
  
  Correct me if I’m wrong! I believe if you just work IFERROR, ” ” into the formula above it would switch your cell to being blank if the dependent list value doesn’t match the corresponding list.
  
  I’m not 100% sure about this as I’m just a novice!
  - Prashanth August 16, 2018 At 8:41 am
    
    Hi, Nicolas,
    
    It won’t work as you suggested!
    
    Thanks.
- Prashanth August 16, 2018 At 8:38 am
  
  Hi, Juan Dela Cruz,
  
  If you delete the value in Column A, the existing value in Column B won’t go. You should delete it manually. But you can see that there won’t be any values in the drop-down in Column B to select.
  
  Maybe I can work on that. Including a new column other than the column C, D, E, and F that contain blank =Char(30)
  
  But I opt to No to avoid further complicating the formula.
  
  Thanks.
John July 21, 2018 At 5:33 am

I found this script some time ago to dynamically create dependent dropdown lists based on name ranges.

function depDrop_(range, sourceRange){ var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build(); range.setDataValidation(rule); } function onEdit (){ var s = SpreadsheetApp.getActiveSheet(); var aCell = s.getActiveCell(); var aColumn = aCell.getColumn(); if (aColumn == 2 && s.getName() == "Sheet1"){ var range = s.getRange(aCell.getRow(), aColumn + 1); var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue()); depDrop_(range, sourceRange); } }

You need to create a separate sheet with the dropdown lists and create name ranges for each option.

Col A | Col B | Col C …
1 Item1 Item2
2 Item1 Item1a Item2a
3 Item2 Item1b Item2b

Create a Name Range for Col B and Col C, ect.. script work perfect for multiple rows.

Cheers!
- Prashanth July 21, 2018 At 9:53 am
  
  Hi, John,
  
  Thanks for sharing the script here and the credit goes to the concerned.
  
  Being an active Google TC in Sheets, I have also seen this / similar script.
  
  I am not familiar with scripts and my attempt was to prove that the dynamic drop-down is possible with formulas too but with limitations.
  
  Many experts think it’s not possible even in a limited way!
  
  So I wrote this post for the educational purpose.
- Juan C. Miranda November 14, 2018 At 9:22 pm
  
  Hi John!
  
  I’m not familiar with script use. Do you know where I can find how to use and correctly configure this script so I can use it?
  
  Thank you!
  - Prashanth November 15, 2018 At 10:26 am
    
    Hi, Juan,
    
    I highly recommend you to shoot your questions on the Google Apps Script G+ community.
    
    Update: Link removed. As far as I know, the ‘G+ community” is no more existing or moved.
Michele Santoro May 30, 2018 At 3:40 pm

Thank you very much for this Prashanth, it’s working perfectly for my needs. I used normal ranges instead of named ranges, created a source categories \ sub-categories sheet which is linked to a “master” data entry sheet, which has to be configured row by row only once, being possible to duplicate it as many times as I need and at the same time being possible to update the categories \ sub-categories values.
Thanks again, I looked for such kind of solution for long and now I can develop my organization’s project management tool as I wanted.
- Prashanth May 30, 2018 At 5:13 pm
  
  Hi, Michele,
  
  I am glad that it worked for you!
  
  I was actually expecting negative reviews as this tutorial is little tough to follow and the formula is not flexible enough. But it’s a unique attempt.
  
  I am a non-native English speaker that sometimes affect my tutorials negatively.
  
  Cheers!
  Prashanth KV
  - Michele Santoro May 31, 2018 At 3:28 pm
    
    Hi Prashanth,
    I tried several other methods found on the internet and yours is the only that actually works (for me) without scripts and leaves open more opportunities of improving its use.
    I do agree that it’s not flexible enough when you have to create different formats of data sheets, but if you have to replicate the same format on different sheets, it’s quite easy and flexible, allowing also updatable categories and subcategories.
    My english is also not native, so my apologies if I’m not making clear enough…
    Keep up with your work and suggestions.
froilan esteves May 7, 2018 At 6:24 am

hi,
can i request for the editable sheet so i can use it in my office inventory.thanks.
Wayne Schomaker April 20, 2018 At 2:04 am

Hi,

I am having the same problem as Luca and 12345 above. I get the same ERROR message, I copy the formulas and use your same cell locations. If there is any resolution to this issue. Thanks!
- Prashanth April 20, 2018 At 9:08 am
  
  Hi,
  Please see the link shared at the end of the post under “Conclusion”. Open the file. For full access feel free to make a copy from the FILE menu in Google Sheets. Copy the formulas.
Debbie Golightly April 10, 2018 At 10:24 pm

Can I get a copy of the dynamic dependent dropdown sheet? Please, it will make a project I am working on much easier. Thank you 🙂
12345 April 5, 2018 At 4:54 am

Hello,

I have copied out your whole example and have used the same names and cell numbers. However, I can’t get the last formula to work it comes up with #ERROR (formula phrase error). Can you please assist.
Thank you
- Prashanth April 5, 2018 At 5:43 am
  
  Hi,
  Let me send you an editable copy of this dynamic depended dropdown list today! Hopefully you can check the formulas.
  Thanks.
Sigri44 April 4, 2018 At 7:02 pm

Hi guys, thanks for this topic !

I have a problem, i want to make a multiple dropdown list but it’s don’t work.

Can you help me ?
- Prashanth April 4, 2018 At 9:52 pm
  
  I’ve shared my sheet with you! You can make use of that. If you want to make it on a large number of rows, it’s not suggestible as it can slow down your sheet.
Jeff March 17, 2018 At 8:54 pm

Nice Idea … but I just built up the system and discovered it’s fatal flaw …

“Then, in cell B2, B3, B4 etc. change the data validation range as G2:T2, G3:T3, G4:T4…”

The updating of the data validation definition …. for every row! … makes this a very clumsy operation and something my users (nor I) really want to do. And sort of defeats the purpose. We have hundreds and hundred or rows of data subject to validation, its just not practical.

I applaud the effort, but its still doesn’t solve the problem.

Google just needs to allow =indirect() in the data validation (like excel does).
- Prashanth March 17, 2018 At 10:23 pm
  
  You’re absolutely right. It’s useful if the number of rows are limited. My intention was to make it public that it’s possible in Google Sheets using formulas. In Google Sheets, you can use scripts for dynamic depended drop down list. Many users use it. Just Google it to find.
  Thanks for the drop by 🙂
  - Leonardo May 21, 2018 At 10:52 am
    
    I’ve searched too much for days, months and it’s not possible yet. I think I will go back to excel.
    - Prashanth May 21, 2018 At 11:24 am
      
      Hi Leonardo,
      
      Google Sheets has that limitation. With my tutorial above you can overcome it to some extent (in a small dataset)
      
      All I can do is to give you my sample sheet with full access. Here it’s (other readers can also follow this link)
      
      https://docs.google.com/spreadsheets/d/1xe15-OBAe183XlJTh_mPwo67CEeQXaCcKY3UloXA2oo/copy
      
      But if you are looking to use the dynamic drop-down in a large set of data, you may want to try Script.
      
      Thanks.
    - Skorch January 11, 2019 At 3:29 pm
      
      Isn’t it possible to do that with the ROW formula?
  - Jottardsen October 15, 2019 At 2:15 am
    
    There’s a solution for multiple rows with google script here:
    
    https://stackoverflow.com/questions/47619562/how-to-copy-data-validation-in-a-relative-way-specifically-list-from-range-wit
    - Prashanth October 15, 2019 At 8:32 am
      
      Thanks for sharing 🙂
- Prashanth February 2, 2021 At 11:48 am
  
  Hi, Jeff,
  
  Now no need to change the reference in the data validation one by one. Updated the tutorial.
  Thanks, Google for the new update.
Luca March 1, 2018 At 6:31 pm

Hi!
I’m currently trying to make an order form for textiles and want to use your method to have dependent drop down menus f.e. product > color > motive, etc. but i can’t seem to get it to work, I always get a parsing error. I made all the steps exactly as shown and just altered the inputs and named ranges etc. to make them fit my topic. Can you help me in any way?
- Prashanth March 1, 2018 At 10:26 pm
  
  Hi,
  I’ve shared the file. You can check your mail.
  Thanks!
  - Denise January 10, 2021 At 9:40 pm
    
    Hi! I know this is a few years late but I’m also getting a “formula parse error” can you help me too.
Alex January 30, 2018 At 4:12 pm

brilliant
too bad there is no option in Google Sheets to change the range automatically.
- Prashanth February 2, 2021 At 11:45 am
  
  Hi, Alex,
  
  It’s now supported! I’ve updated the post.
  - Bhavesh Gwalani February 8, 2021 At 2:13 pm
    
    I’m not able to get this new option, do you know how do I get that option?
    - Prashanth February 8, 2021 At 2:38 pm
      
      Hi, Bhavesh Gwalani,
      
      In your Sheet, in any blank cell do as follows.
      
      Go to the menu Data and click “Data validation”. Select the “Criteria: List from a range”.
      
      You can see the below note just above “Show drop-down list in a cell”.
      
      “Tip: Use absolute references (e.g. =$A$1:$B$1) to lock rows & columns.”
      
      If it’s not showing, the update is not yet fully rolled out by Google. Please wait for a few more days.
      - Josh February 9, 2021 At 11:10 am
        
        Hi Prashanth,
        
        I see the text you reference but am having the same issue. Ranges aren’t updating automatically. Every time I click back into data validation the relative reference I last saved has been locked as an absolute reference. Any ideas?
      - Prashanth February 9, 2021 At 12:14 pm
        
        Hi, Josh,
        
        Include = sign. For example, you should write the reference as =Sheet1!G1:T1 not Sheet1!G1:T1.