This is indeed ONE of the ways to do the multi row dependent drop down list. This alternative is actually 👍👍👍 if we're just seeing from the 'result'. But if we are building a sheet, with the dependent drop down list is just ONE of the features, it's kinda ineffective since the data repeated over and over again -> increasing the memory -> slower sheet Cheers to another pov of spreadsheet formula tricks 🙌
Thanks for the feedback. And absolutely it could bog down the memory here. I'd love to hear any ways that wouldn't bog down the memory. I would secretly love if Google would add the ability to determine the drop down values from a formula right in the data validation. It would make it oh so much easier. In theory it might be possible through an appScript or something like that... but thats something for another video in the future. If there is any topics you would love to see us cover, we'd love to know about it.
@@_TheFirstPancake_ Well since you mention it, here's another :) I have a table of 7 columns, each with a named heading. I need a function that will match the text in an adjacent cell against the entire 7-column range, then display the appropriate heading under which that text is found. For context, I'm a pastor logging my sermons, and one column lists the book of the Bible for that sermon. I want the cell to the right to automatically list what genre of writing that book corresponds to, and on another sheet I already have all 66 books sorted into 7 genres. Does that make sense? I'm pretty new at this, so I don't even know what to search for, except it has got to be some variation of an IF function. Thanks in advance!
There must be less data repitive method to creating these dependent drop down list. Correct me if I'm wrong but with Excel we can just use =INDIRECT with the dependent cell referenced usually piggy backing off of Named Ranges titled the same as the referenced cell. I feel like Google has made a fairly simple function overly complicated especially for the average user. Personally, I'm really concerned about using this repetitive method and how it will affect speed and functionality with larger files. I wonder if there is an alternative for Sheets similar to INDIRECT as I described for Excel?
Hi Nicolas, I absolutely agree that this could be heavy for the spreadsheet. I've never seen this method using INDIRECT before. Sheets does have INDIRECT. Do you have an example of what this could look like? Let me play with it and see if I can do what you are referring to. ;)
Is there any way when youre done to sort the sheet now by, for example, name of your friend? I tried doing that in mine and it messed up all my data validation categories. Any advice?
My question is, how do I get something from the drop down list to populate on another sheet? For example I have a drop down list with "Absent, complete, testing". I want "Absent" to generate on another sheet so i can keep track of who was absent.
Hi Estefany, Thanks for reaching out. If you have them in one big sheet, you can move them to another tab using and =FILTER to get just that group, or you can use =SORT if you want to group them. If you need them in completely different spreadsheet files, then you would have to combine =FILTER and =IMPORTRANGE. If you are just looking for totals, you could also use a pivot table to get the overall number of times. I hope this helps.
For the last 1 week I was desperately searching for Multi Dependent Dropdown list function. I got the clarity to a great extent thru this video. My situation: I have a list of 36 provinces of India & Each provinces have 5-35 districts. Now, I am preparing a list of 2600 Trained Technicians (nationwide), Where I want their Districts drop downs should be based on “provinces” selection. This feature is important as this (Master Data) sheet will be accessed by 25 Individual engineers spread throughout the nation and they will take care of listing their Area technicians details. Your idea of keeping a duplicate sheet (hidden) of the main sheet can solve my problem for now I guess. But, I would request, if their could any simpler solution for this topic (like in excel) Tons of Thanx!
We are so happy to hear that this helped you. I'm working on finding a simpler solution. Someone in one thread suggested something involving tables and named ranges. We are trying to figure out the best way to do this. So glad that this worked for what you need right now. please let us know if there is anything else you are stuck with and maybe we can create a video around it.
@@_TheFirstPancake_ Thank you team & I will actively wait for you to come up with some easy alternative of the present demonstrated workaround in G-Sheet. Meanwhile, a support required! do you have any solution for "Multi select" Data validation list. The end result should be comma separated items in the single cell. I understand this feature is available in Excel. But, if you have something similar for G-Sheet, it will be so helpful!
@CprtFM Thank you for your patience. 2 questions. 1) Could you clarify the above. I'm not sure I understand what you mean regarding a data validation list. I can certainly take a bunch of data and add it to a single cell, comma separated but I'm not clear on how this will validate data. 2) Could you confirm that this list if a fairly comprehensive list of provinces and districts in India? vikaspedia.in/education/current-affairs/states-and-districts-of-india
This is indeed ONE of the ways to do the multi row dependent drop down list. This alternative is actually 👍👍👍 if we're just seeing from the 'result'.
But if we are building a sheet, with the dependent drop down list is just ONE of the features, it's kinda ineffective since the data repeated over and over again -> increasing the memory -> slower sheet
Cheers to another pov of spreadsheet formula tricks 🙌
Thanks for the feedback. And absolutely it could bog down the memory here. I'd love to hear any ways that wouldn't bog down the memory. I would secretly love if Google would add the ability to determine the drop down values from a formula right in the data validation. It would make it oh so much easier.
In theory it might be possible through an appScript or something like that... but thats something for another video in the future.
If there is any topics you would love to see us cover, we'd love to know about it.
Amazing! Great video, it works with +1000 lines!! Thank you!
Thank you so much! Your tutorial is the easiest and the most straightforward!
Glad it helped! Let us know if you have any other ideas that we can turn into videos
Thank you very much friend, I tested it on a spreadsheet with more than 150 thousand lines and it is working very well!
My goodness, that’s a heck of a spreadsheet. Good for you.
What other problems are you looking to solve?
EXACTLY what I needed. THANK YOU!!!
Thank for dropping a comment. Glad we could help.
Let us know what other content you need and we can see what we can do.
Pretty complicated but really well explained
Thanks for the feedback. If there are any topics you would like use to cover, please let us know.
FINALLY! Thank you for showing how to do this!
Happy to help! Let us know if you have any other ideas you want to see turned into videos.
@@_TheFirstPancake_ Well since you mention it, here's another :) I have a table of 7 columns, each with a named heading. I need a function that will match the text in an adjacent cell against the entire 7-column range, then display the appropriate heading under which that text is found. For context, I'm a pastor logging my sermons, and one column lists the book of the Bible for that sermon. I want the cell to the right to automatically list what genre of writing that book corresponds to, and on another sheet I already have all 66 books sorted into 7 genres. Does that make sense? I'm pretty new at this, so I don't even know what to search for, except it has got to be some variation of an IF function. Thanks in advance!
Question, this worked great until I wanted to sort my main sheet in order from A->Z. It messes everything up - why is that?
Could you share the file you are working on in the video? It's much easier to follow while clicking yourself :)
love it thanks you so much !!
You are very welcome. Please let us know if there is anything else you would like to learn. We love getting content ideas from our community.
There must be less data repitive method to creating these dependent drop down list. Correct me if I'm wrong but with Excel we can just use =INDIRECT with the dependent cell referenced usually piggy backing off of Named Ranges titled the same as the referenced cell.
I feel like Google has made a fairly simple function overly complicated especially for the average user. Personally, I'm really concerned about using this repetitive method and how it will affect speed and functionality with larger files.
I wonder if there is an alternative for Sheets similar to INDIRECT as I described for Excel?
Hi Nicolas,
I absolutely agree that this could be heavy for the spreadsheet.
I've never seen this method using INDIRECT before. Sheets does have INDIRECT. Do you have an example of what this could look like?
Let me play with it and see if I can do what you are referring to. ;)
Is there any way when youre done to sort the sheet now by, for example, name of your friend? I tried doing that in mine and it messed up all my data validation categories. Any advice?
Having the same problem
If you figure this out, can you please let me know? I'm trying to troubleshoot
Can u make vedio for Google Calendar and Google sheet reminder
Happy to give it a try.
What are you wanting it to cover?
My question is, how do I get something from the drop down list to populate on another sheet? For example I have a drop down list with "Absent, complete, testing". I want "Absent" to generate on another sheet so i can keep track of who was absent.
Hi Estefany, Thanks for reaching out.
If you have them in one big sheet, you can move them to another tab using and =FILTER to get just that group, or you can use =SORT if you want to group them.
If you need them in completely different spreadsheet files, then you would have to combine =FILTER and =IMPORTRANGE.
If you are just looking for totals, you could also use a pivot table to get the overall number of times.
I hope this helps.
For the last 1 week I was desperately searching for Multi Dependent Dropdown list function. I got the clarity to a great extent thru this video.
My situation: I have a list of 36 provinces of India & Each provinces have 5-35 districts.
Now, I am preparing a list of 2600 Trained Technicians (nationwide), Where I want their Districts drop downs should be based on “provinces” selection. This feature is important as this (Master Data) sheet will be accessed by 25 Individual engineers spread throughout the nation and they will take care of listing their Area technicians details.
Your idea of keeping a duplicate sheet (hidden) of the main sheet can solve my problem for now I guess. But, I would request, if their could any simpler solution for this topic (like in excel) Tons of Thanx!
We are so happy to hear that this helped you. I'm working on finding a simpler solution. Someone in one thread suggested something involving tables and named ranges. We are trying to figure out the best way to do this.
So glad that this worked for what you need right now. please let us know if there is anything else you are stuck with and maybe we can create a video around it.
@@_TheFirstPancake_ Thank you team & I will actively wait for you to come up with some easy alternative of the present demonstrated workaround in G-Sheet.
Meanwhile, a support required! do you have any solution for "Multi select" Data validation list. The end result should be comma separated items in the single cell. I understand this feature is available in Excel. But, if you have something similar for G-Sheet, it will be so helpful!
@CprtFM Thank you for your patience. 2 questions.
1) Could you clarify the above. I'm not sure I understand what you mean regarding a data validation list. I can certainly take a bunch of data and add it to a single cell, comma separated but I'm not clear on how this will validate data.
2) Could you confirm that this list if a fairly comprehensive list of provinces and districts in India? vikaspedia.in/education/current-affairs/states-and-districts-of-india