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.
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?
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!
Same for me. I would love to change my accouting sheet from Excel to Google sheet. But with over 2000 rows this indirect will not work. in Excel I use indirect(A?) and it works
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.
@@carlysorensen2198 have you managed to solve this ? after some digging i found out you can do it by adding a google script. hope it helps (watch?v=lIjrevuWMB8)
I just spent hours creating unique data validation criteria for 50 rows, which of course required 50 unique indirect functions on a separate sheet. My head is pounding!
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?
Say your list of fruit starts at D5 and goes to D50 but you need to make sure you can add infinite items to your list in column D, then you would do D5:D - This will infinitely select column D starting on row 5. You can also make it say like D5:F and it will select all cells from column D to F, from row 5 down infinitely. Say it starts at D5 and goes to Z5 but again you need to make sure you can keep adding items without having to update formulas each time then you should do D5:5 - Again you can have items on multiple rows, say every row from D5 down to D7 all the way over as far as you want/need has info in it- then you can do D5:7 which would select everything from row 5 to row 7 starting at column D infinitely
@@GusTheAnt That didn't answer Husna's question, which is the same as mine. I have literally hundreds of "fruit" rows that need the dependent drop down list. Column A: Initiative (manually added) Column B: Big Priority category (eg Fruit, this is the big bucket of priorities) Column C: (depending on what is selected in Column B) list of departments that work on those Big Priorities (eg fruit varietals) Therefore, there can be infinite additions in Column A which need to select one item from the drop down menu in Column B and then 1 item from the drop down in Column C which is dependent on Column B Besides doing it manually, I can't figure out a way to add hundreds of rows with the multiple drop down menus that are dependent. Super frustrating
Just doens't work for high volume of lists. I'm trying to find out how to make this for thousands of items, but without a native function inside the data validation I think it's just impossible.
@@anmerpozzobon9083 Indeed, it just doesn't seem possible in GSheets. In Excel it seems to work just fine, as it "adapts" the formula when you extend the rows. I've read about scripts that help doing it, but haven't been successful in implementing any. If anyone has a solution for Dependent Drop Down Lists in GSheets, please, mark me on the comment / video :)
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.
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!.
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]
You can also use the SUBSTITUTE() function to change the space to an underscore when looking at a name in the list so it still works this way. Example based on his example: =INDIRECT(SUBSTITUTE(B4," ","_")) So if someone selected "Lady Finger" it would convert it to "Lady_Finger" and then pull that named range.
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
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?
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!
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.
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!
Great tutorial. I'm having a problem where this only works on the first row of my drop down, even though the data range is extended the the last row. Any ideas on how to fix this?
Couple of points: 1. Named ranges aren't case sensitive, so you can write "bananas" and it will still work. 2) It's not a good idea to use multiple named ranges for the amounts remaining - instead this should use vlookup or similar. That way you won't need to use an underscore in the names for the fruit ("Lady_Finger") which is required for named ranges, but not for vlookup.
Thanks for the video. is there any way to copy the multiple dropdown lists with another command point because I have multiple cells and don't want to write an indirect formula for each one?
What if I have a very long list of different things? for exqmple, I'm doing expenses and just want a simple dependency table for categories and sub categories. Do I have to go through every single row and do this?
Hi if I want to create a whole column of drop downs do I need to repeat the process for every single cell/ drop down or is there a bulk way of doing this.
Hi, thanks so much for the video! Just a question - if I use this method for tracking clients I work with, some of whom may have multiple funding sources, let's call them Fund A, Fund B, etc. but not all do, then would have available hours of funding within those categories, could I use this process for that workflow?
Hi there, thanks for your video. I realize my question is not directly related to your video but I was wondering if Google sheets has the capability to create a simple drop-down menu with items that are removed from the list when selected? Thanks for your reply in advance!
Please help this teacher! I create my lesson plans on an excel sheet. I would like to use your advice for drop down features and formula to insert the lesson standards for each activity I teach. I think I can follow your steps in this video to create a list of standard codes, However because there are so many and I don't have them memorized I am looking for a hack. .. so bear with me. Is there a way to view a whole sentence (each standard) in the drop down, but only have the standard code appear in the lesson plan document when chosen from the drop down list? For example, if the standard in the drop down reads "I.A.a.1. 1. Engages in physical activities with increasing balance, coordination, endurance and intensity", when selected from the list I would only like only "I.A.a.1.1" to show on the actual plan. So I want both options available: to view the whole sentence But only insert the code to the lesson plan. I cover between 1-6 standards per activity so i don't have the space to write each one in a whole sentence in that cell. I hope I asked my question clearly enough.. and more than ever I hope you have a solution that could help me! Thank you!
This seems to be fine for simple things, i don't think Sheets handles the named range very well (you can't search for what you created in the named range window). The underscore between words looks bad and is cumbersome to type. It did get me started and atleast i found the problems early, but i wouldn't think this more than an example and a direction to something that can handle hundreds of entries (easily) would have most desired.
Hi- I am a school counselor and am trying to connect information from a master sheet (contains all of my caseload as well as information pertaining to my students) and I need to get that information to individual sheets for each student so it is personalized for them and only contains their information. I will have over 400 students. Is there a way to do this in an efficient way?
I tried to do this, but in my first drop-down EG (apples) I have 5-15mm door bottom gap, I have managed to make the indirect function work if I shorten the the name to (Bug) however that's not going to work out when i have hundreds of different itemised faults that require a selection of rectifications
Greetings! Thank you for all your amazing symposiums. My question is can you create a Dropbox for typing in the form of to be embedded? I'm trying to be able to type on doc pdf . I need an to embed on the application. Thx in advance
Hi, great video,I'm a window cleaner and was wondering if instead of fruit I had addresses in column A and in column B I had payment methods Ie: cash,cheque, card, Bank Transfer but it was all mixed up, and in column C is price, is there a way of extracting all the cash and adding it up then the same with card and so on. Hopefully this makes sense
What is I need to build a multiple list which can scale ofer say 100-200 odd entries? Is there a simpler way where I don't have to create endless named lists linked indirect columns?
When I add a Named Range, you can only use 1 word so if I am trying to create a list of items that have multiple words for the name, I cannot create a named range that matches so this doesn't work. Any other ideas?
Great videos, could you share a case in which we have multiple dependant validation such as location validation (province, perfecture, and districts are depending on each other). I made one with MS Office, and when I open it on google drive, they just read my match formula results instead of showing the list. I'm putting together indirect and concatenate function to call my name ranges. Please help
Hi , will be grateful for help If i have bananas and their types below, and than oranges and their types right below, and so on (when all the ranges are in one column), how could I create a drop-down list? Is it possible to choose a separate cell for that drop-down? Many thanks 💚
hello, need your help to create a dependent dropdown in *google form* using data or dependent dropdown menus from a google sheet. please guide me or if possible make a video on it. P.S : Don't want to use logic based section shuffle or *form ranger* or *cascade formatting*. thanks in advance
How do we get rid of the "invalid" warning when we go back and select a new value from the first drop-down? This seems to be intentional with Google Sheets, i.e. not an error, but it quite distracting to user of a sheet with multiple drop down lists. Any idea how to get rid of it?
And this is why Excel is so much more advance as you can just do an indirect for each row within the Data Validation Drop down so you don't have to create multiple lists everywhere which is not ideal if you have 100's of rows of drop downs. PLEASE can someone tell me there is a better way to do this?
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.
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?
Still Stuck? I can't find the way too.
Excel can do this by using the indirect formula in the data validation, i have no idea why we cannot do this with Sheets.
@@blackmartini7684 True... can't understand why it doesn't work with google sheet... this is ridiculous!
ruclips.net/video/p4bXhNH93sI/видео.html
"I have the solution. Very soon, I will share the video with you."
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!
What if you have 1000 dropdowns? Do we need create 1000 indirects as well?
Same question I have.
Same for me. I would love to change my accouting sheet from Excel to Google sheet. But with over 2000 rows this indirect will not work. in Excel I use indirect(A?) and it works
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.
Agree. I can't figure out a way to add hundreds of rows without a separate sheet that has all the Indirect functions manually created...a total pain
@@carlysorensen2198 have you managed to solve this ? after some digging i found out you can do it by adding a google script. hope it helps (watch?v=lIjrevuWMB8)
@@carlysorensen2198 You can use this method. Only some quick additional step ! 👍ruclips.net/video/uuC24mFV8CY/видео.html
Kind of wild that Sheets is 99.99% similar to Excel except for this exact thing.
I just spent hours creating unique data validation criteria for 50 rows, which of course required 50 unique indirect functions on a separate sheet. My head is pounding!
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?
Say your list of fruit starts at D5 and goes to D50 but you need to make sure you can add infinite items to your list in column D, then you would do D5:D - This will infinitely select column D starting on row 5. You can also make it say like D5:F and it will select all cells from column D to F, from row 5 down infinitely.
Say it starts at D5 and goes to Z5 but again you need to make sure you can keep adding items without having to update formulas each time then you should do D5:5 - Again you can have items on multiple rows, say every row from D5 down to D7 all the way over as far as you want/need has info in it- then you can do D5:7 which would select everything from row 5 to row 7 starting at column D infinitely
@@GusTheAnt That didn't answer Husna's question, which is the same as mine. I have literally hundreds of "fruit" rows that need the dependent drop down list.
Column A: Initiative (manually added)
Column B: Big Priority category (eg Fruit, this is the big bucket of priorities)
Column C: (depending on what is selected in Column B) list of departments that work on those Big Priorities (eg fruit varietals)
Therefore, there can be infinite additions in Column A which need to select one item from the drop down menu in Column B and then 1 item from the drop down in Column C which is dependent on Column B
Besides doing it manually, I can't figure out a way to add hundreds of rows with the multiple drop down menus that are dependent. Super frustrating
Just doens't work for high volume of lists. I'm trying to find out how to make this for thousands of items, but without a native function inside the data validation I think it's just impossible.
@@anmerpozzobon9083 Indeed, it just doesn't seem possible in GSheets. In Excel it seems to work just fine, as it "adapts" the formula when you extend the rows. I've read about scripts that help doing it, but haven't been successful in implementing any. If anyone has a solution for Dependent Drop Down Lists in GSheets, please, mark me on the comment / video :)
Also very interested in this solution - adding myself to this list.
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.
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!.
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]
You can also use the SUBSTITUTE() function to change the space to an underscore when looking at a name in the list so it still works this way. Example based on his example: =INDIRECT(SUBSTITUTE(B4," ","_")) So if someone selected "Lady Finger" it would convert it to "Lady_Finger" and then pull that named range.
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
GOD Thank you!
How does nobody figure out that is the top commend !
WhWow after watching hours of videos - you had the best way and so easy to follow! Subscribed for sure!
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?
i had the same question!
@@ashley5352 and me! Still searching
You are an excellent teacher!! Thanks so much for this video. So clear and helpful
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!
hey! do you have an answer? bcuz i have the same problem :(
I have 1000 row
Thank you for this, you helped me with a few tables I needed to populate for a character sheet I made!
and how to apply second dropdown for 200 rows and 15 columns data? you can't do it manually, right?
is this still applicable if you have a thousand rows to work with?
Seems like is not, there is no answer here regarding this :(
What if I have 100 rows?
hey, did you find the answer?
Wow, great video. Fixed a problem I've been trying to solve for years
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.
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!
Great tutorial. I'm having a problem where this only works on the first row of my drop down, even though the data range is extended the the last row. Any ideas on how to fix this?
Couple of points: 1. Named ranges aren't case sensitive, so you can write "bananas" and it will still work. 2) It's not a good idea to use multiple named ranges for the amounts remaining - instead this should use vlookup or similar. That way you won't need to use an underscore in the names for the fruit ("Lady_Finger") which is required for named ranges, but not for vlookup.
I just put in practice your training, it was perfect, so easy and efficient, thank thank thanks...
Thank you so much. you save me from so much trouble
Thanks for the video. is there any way to copy the multiple dropdown lists with another command point because I have multiple cells and don't want to write an indirect formula for each one?
Its mindblowing that google does not have a solution for that, and we need to do hacks like this.
The same thing may be done using INDEX and MATCH functions. No issue with spaces also.
explain?
Thanks... I really really really appreciate that you share your knowledge....
What if I have a very long list of different things? for exqmple, I'm doing expenses and just want a simple dependency table for categories and sub categories. Do I have to go through every single row and do this?
But this does not work. What if I have 1000 rows? What real-world scenario does this serve?
Hi if I want to create a whole column of drop downs do I need to repeat the process for every single cell/ drop down or is there a bulk way of doing this.
Hi, thanks so much for the video! Just a question - if I use this method for tracking clients I work with, some of whom may have multiple funding sources, let's call them Fund A, Fund B, etc. but not all do, then would have available hours of funding within those categories, could I use this process for that workflow?
Really helpful. Thanks
Hi there, thanks for your video. I realize my question is not directly related to your video but I was wondering if Google sheets has the capability to create a simple drop-down menu with items that are removed from the list when selected? Thanks for your reply in advance!
Please help this teacher! I create my lesson plans on an excel sheet. I would like to use your advice for drop down features and formula to insert the lesson standards for each activity I teach. I think I can follow your steps in this video to create a list of standard codes, However because there are so many and I don't have them memorized I am looking for a hack. .. so bear with me. Is there a way to view a whole sentence (each standard) in the drop down, but only have the standard code appear in the lesson plan document when chosen from the drop down list? For example, if the standard in the drop down reads "I.A.a.1. 1. Engages in physical activities with increasing balance, coordination, endurance and intensity", when selected from the list I would only like only "I.A.a.1.1" to show on the actual plan. So I want both options available: to view the whole sentence But only insert the code to the lesson plan. I cover between 1-6 standards per activity so i don't have the space to write each one in a whole sentence in that cell. I hope I asked my question clearly enough.. and more than ever I hope you have a solution that could help me! Thank you!
Thanks... it helped me a lot..
This seems to be fine for simple things, i don't think Sheets handles the named range very well (you can't search for what you created in the named range window). The underscore between words looks bad and is cumbersome to type. It did get me started and atleast i found the problems early, but i wouldn't think this more than an example and a direction to something that can handle hundreds of entries (easily) would have most desired.
when you click on Bananas (timing 10:57) why B6 not changed, It's showing the range error. Please solve my query
Hi-
I am a school counselor and am trying to connect information from a master sheet (contains all of my caseload as well as information pertaining to my students) and I need to get that information to individual sheets for each student so it is personalized for them and only contains their information. I will have over 400 students. Is there a way to do this in an efficient way?
Beautiful!!! Please do you have any tutorial on Data Analysis?
Thank you
I tried to do this, but in my first drop-down EG (apples) I have 5-15mm door bottom gap, I have managed to make the indirect function work if I shorten the the name to (Bug) however that's not going to work out when i have hundreds of different itemised faults that require a selection of rectifications
Do you have a tutorial on how to do a dropdown from images we can upload?
Greetings! Thank you for all your amazing symposiums. My question is can you create a Dropbox for typing in the form of to be embedded? I'm trying to be able to type on doc pdf . I need an to embed on the application. Thx in advance
thank you so much!
Useless, doesn't help for multiple rows
how to return cell address with XLOOKUP function of a last non-blank cell in google sheets?
Hi, great video,I'm a window cleaner and was wondering if instead of fruit I had addresses in column A and in column B I had payment methods Ie: cash,cheque, card, Bank Transfer but it was all mixed up, and in column C is price, is there a way of extracting all the cash and adding it up then the same with card and so on.
Hopefully this makes sense
What is I need to build a multiple list which can scale ofer say 100-200 odd entries? Is there a simpler way where I don't have to create endless named lists linked indirect columns?
ruclips.net/video/uuC24mFV8CY/видео.html
When I add a Named Range, you can only use 1 word so if I am trying to create a list of items that have multiple words for the name, I cannot create a named range that matches so this doesn't work. Any other ideas?
Great videos, could you share a case in which we have multiple dependant validation such as location validation (province, perfecture, and districts are depending on each other). I made one with MS Office, and when I open it on google drive, they just read my match formula results instead of showing the list. I'm putting together indirect and concatenate function to call my name ranges. Please help
"I have the solution. Very soon, I will share the video with you."
Hi , will be grateful for help
If i have bananas and their types below, and than oranges and their types right below, and so on (when all the ranges are in one column), how could I create a drop-down list? Is it possible to choose a separate cell for that drop-down? Many thanks 💚
what if the 3rd drop down is a choice between 2 values? (please help)
what if I want to make a retail sales list with more than 10 rows, if we make 10 indirect data reference seems not efficient, do you have an advice?
hey, i have the same question. do you know the answer?
@teacher's Tech; how do you like Google Sheets compared to Microsoft Excel ?
hello,
need your help to create a dependent dropdown in *google form* using data or dependent dropdown menus from a google sheet. please guide me or if possible make a video on it.
P.S : Don't want to use logic based section shuffle or *form ranger* or *cascade formatting*.
thanks in advance
How do we get rid of the "invalid" warning when we go back and select a new value from the first drop-down? This seems to be intentional with Google Sheets, i.e. not an error, but it quite distracting to user of a sheet with multiple drop down lists. Any idea how to get rid of it?
only works for one row entry
I don't think it is a best way to multiple dependent drop down list. More rows need more sets of sub categories if you follow his way.
And this is why Excel is so much more advance as you can just do an indirect for each row within the Data Validation Drop down so you don't have to create multiple lists everywhere which is not ideal if you have 100's of rows of drop downs. PLEASE can someone tell me there is a better way to do this?
ruclips.net/video/uuC24mFV8CY/видео.html
very very dirty solution
It so great! mr pleas send to filmor x or pro setup through email
Correcting your own errors is confusing. Please take effort to edit it OUT!
lol, overcomplicated solution for simple data validation dropdown.
Not scalable!
this function high risk, if u have many name range
Useless