Google Sheets - Create Multiple Dependent Drop-Down Lists

In this video tutorial, I will show you how to create multiple dependent drop-down lists in Google Sheets. Using data validation and the INDIRECT function in Google Sheets allows you to create drop-down lists from named ranges. This makes a simple and quick way to make a dependent drop-down list but it’s not without limitations. I will go over these issues and explain why you may not want to use this method.

Multiple Dependent Drop-Down lists in Microsoft Excel:

[craigreinecke]

Solution for the space.
Keep all the regular typed names with spaces like normal. Use the underscore for the named ranges. In the final formula in C6, use the substitute() function to replace the spaces with an underscore. It makes for a longer final formula, but the aesthetic is more human.

=INDIRECT(SUBSTITUTE(B6, " ", "_"))

Great video! Now if only you could use INDIRECT() in the data validation like you can in Excel.

[ricka]

As of Feb 2023, there are some extra steps needed in the Data Validation section (At least on my instance of Google Sheets). In my case I had to click on ADD RULE then select DROPDOWN (From a Range) in the Criteria section. Other than that, it worked well. Thanks!

[ashley]

Okay, but let's say you want 1000 rows to all have the same 2 drop-down options, but that correlate with the corresponding row, without needing to create new "indirect" tables for all 1000 rows. There has to be a way where instead of selecting the dropdown menu cell directly, it will select the dropdown option you chose in the corresponding row. there has to be. But everything I have tried on google/youtube has come back as an error. Can anyone help with this?

[kojakeugenio]

What if you have 1000 dropdowns? Do we need create 1000 indirects as well?

[timheller]

Best explanation I've watched. Most stop after showing how to set up the dependent drop-downs on one line and don't show you how to do that on multiple lines. Thank you.

[crazyg]

it's too bad we can't use the indirect function in the data validation criteria directly, like you can with Excel. That allows you to have multiple rows of the same dependent dropdowns, without needing to put an intermediate list somewhere else. This alone is a showstopper for me.

[ericmcgaw]

I have seen the presenter in a box in the bottom right corner before but I have never seen the cut out silhouette of the presenter! Excellent productions value and video resolution quality - not to mention the great content. Thank you!.

[slowtvkeithmartin]

This is a great tutorial – it explained the process clearly and gave enough context (but not TOO much) to ensure full understanding. UNFORTUNATELY the Google Sheets feature itself is somewhat restricted in its abilities: it doesn't work if the column titles contain spaces OR if they begin with numbers. I see that with more jiggery-pokery with formulae I could use the substitute function to handle this, but it would end up being stupidly convoluted. [Sigh]

[husnayahaya]

What if the "Fruit" column is infinite - It's not going to be just 3 rows, it's going to be hundreds of rows, I don't think the Indirect function is suitable function as you need to create the indirect hundreds of times. Is there any other way to go about this?

[yvettelara]

WhWow after watching hours of videos - you had the best way and so easy to follow! Subscribed for sure!

[cheerbear]

So I figured it put, if someone hasn't already. You haven't to rearrange your list to go horizontal. You can do that with copy then paste TRANSPOSED. Then reconfigure your named ranges for the new cell ranges. Where your filtered results go you do have to drag the indirect formula down make sure with no $ signs for every rows you want. Then go back to your data validation drop down list remove the $ infront of the row numbers but not columns and you are able to drag it down and will give you independent lists for every row. Hope that makes sense.. it worked for me

[ManjaMcMills]

You are an excellent teacher!! Thanks so much for this video. So clear and helpful

[liveautomationexperts]

Great Video. However, for each row, do we need to create separate column of data with indirect? It can be easily manageable in Excel in this case. The Named range automatically, takes the next row as reference and updates the data validation data. Usually, with offset and match formulas in sorting order. If I have 1000 rows, then it is quite a big task. Any alternatives for this with formula instead of scripting?

[chingsdiary]

Yeah~ it works! I used the template for quite a while for accounting and always wanted to improve and customized some of the functions but didn't know how. Thanks for sharing!

[TastyTarrasque]

Thank you for this, you helped me with a few tables I needed to populate for a character sheet I made!

[mfoisy]

Wow, great video. Fixed a problem I've been trying to solve for years

[bloodmoongrizzlythefirst]

Great video. I would recommend an update on it, much has changed in 2 years. It has pointed me in the right direction but i had to to much more "exploring" to get it to work.

[monalisahota]

The last part where you'd need to manually create data validation for each dependent cell does not seem right. I tried the rest of it and it works and is very helpful. But I have 400 rows and I am going to keep adding more rows to my sheet. There must be another way to do data validation for the whole column. If anyone knows the answer, could you please let me know. Other than that, great tutorial!

[mariopuebla]

I just put in practice your training, it was perfect, so easy and efficient, thank thank thanks...

[mangafreak]

Thank you so much. you save me from so much trouble