At this point, I've practically seen every single video on a multi-row dependent drop-down list, but this is the easiest and fastest way to do it. You, my friend, are a life saver!
For a long, I am searching for this solution and tried many videos, to do the same, but could not get success. As most of the tutorials are not so clear. But this is very easy and simple to understand. I really appreciate your efforts to make this tutorial simple to understand. Thank you very much and keep it up.
I've been looking for a solution for days and this tutorial saved me!! Thank you! This wasn't the first video I saw that used the transpose thing but you explained it much better.
Very helpful tutorial. One thing that your video did not discuss though is that on your "Notes" tab column E has to be copied (I did past 100 to test). It will show blank (as discussed) until information is put into your "Main" tab (such as A60). Then the information on your "Notes" tab will have info.
Really made it simple and easy to understand. Only one thing I would like to know is if I have selected value from drop down in B2 and then I change the value of A2, it shows invalid cell but does not clear it. Is there any way to clear the cell as I am using the combination of 2 values to get final result. Be my lifesaver too ;)
Hi chirag110, unfortunately your only way of doing this would be with a little Google Apps Script code with the onEdit() function trigger. Basically you want to listen for changes to col A and then use offset to change the corresponding cell in Col B. Similar to this approach for adding static date time stamps: yagisanatode.com/2018/02/21/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/
Thank you so much for this amazing tutorial, very helpful! Just wondering if I can apply the same formula to the next column. E.g. After I select the cell on column B from the drop down list, then on column C, it will appear another filtered drop down list.
Yes, you can. You will need to extend the Notes sheet to handle this, but it will work. Once to get to a large number of dependent dropdowns, I recommend switching to Google Apps Script to either do this onEdit() or with a Sidebar or Dialog box input.
Love the video and how you explain everything. Maybe I missed this. on my main tab - I have category (A) and subcategory (B). I can drag the categories all the way down.. no problem. but can't get the subcategories to populate all the way down the page. Any thoughts. Thank you.
Hi there, just want to thank you for the video! I have a very similar setup I'm trying to implement in our Wholesale Order sheet. Currently everything works, except for when an order is complete and it's time to remove it from the list; the only way I've found that doesn't break the dynamic dropdown is to copy ALL the data beneath the 'obsolete' order, paste it on top of the old order, and then manually remove whatever was left at the bottom that is now duplicate. Is there a way to delete rows mid-sheet without wreaking havoc on the cell references? I'm not actually using FILTER, just ArrayFormula, TRANSPOSE and a whole lot of nested IF statements (I modified from another tutorial I'd found). Is there a way to use Scripts instead to, on delete of a row, force the dynamic data validation references to shift up relatively?
I don't have a non Apps Script solution that comes to mind. Perhaps an ARRAYFORMULA on the dropdown options. If I get a chance I will take a deeper look.
@@yagisanatode Hey, thank you for the response! No worries, I had figured it would need to be Script - I will look around for some solution there. I've successfully used scripts in the past to automate things like email responses, but haven't had success yet with the more SQL-esque behaviors I need. I will keep trying!
Im doing a yearly production record. I can (and have) made a dependent drop down with the first column representing our brands and the second representing individual products that brand offers. Im trying to make each months production (aka one sheet) have the dependent drop downs. Does that mean i need to create a cell reference for each sheet?! Or is there a way to have the dependent drop downs show on each sheet with just the one "master" set of cell references? Im also aware this may be an app script thing but im hoping not...
I have a question. I am trying to make multiple dropdowns of the same list of items, but I want that list to exhaust itself after all the dropdown options have been chosen. Here's what I mean: I have 4 dropdown lists containing 4 options each. In dropdown list #1, I have options A, B, C, and D In dropdown #1, if I select option D, dropdown #2 will exclude option D; only options A to C will be available. Now, in dropdown 2, if I select option A, dropdown #3 will only contain options B and C. That's what I am trying to do. So how do I perform this set of operations? If I continue on with that pattern and select option C, then dropdown #4 will only have one available option which is B. For clarity, the reason A, C, and D are not available in dropdown #4 is because they were already selected in dropdowns #1, #2, and #3. Thank you for your time.
Hi MasterOFSuperFunny, Yes. I provides some bonus information in my written version of this tutorial to answer your questions along with an accompanying example Google Sheet that you can find here: yagisanatode.com/2021/07/04/update-a-range-of-dropdown-lists-in-a-google-sheet-dynamically-based-on-a-previous-dropdown-choice/#Bonus_1_-_A_dropdown_list_family_with_options_that_are_removed_after_each_selection
How do you maintain the integrity of choices when you sort your Option 1 column? (Or sort all your data by a different column)? All the validation doesn't stay aligned when the data is sorted.
Hi TDW Box Office. A good option her is to use the filter tool. ruclips.net/video/fghVqHu9qdI/видео.html Or you could use some data validation. Alternatively, ensure that you select the entire range of the data before filtering it.
I am yet to find out how to make a entry row that gets the information put in first six cells postponed one or two rows below as soon as I change the status of the cell six of that first row. The idea is to keep the most recent data on top automatically and keep using first row as a data entry row. Not sure if it can be done
Hi Liyun Jin, Yes, this approach is not ideal when combined with using filter. The better solution would be to use come Google Apps Script to provide options. However, if you don't want to go this approach. You could reference the range in another Google Sheet tab and apply a SORT function based an a sort dropdown. I've added an extra tab in the Google Sheet tutorial for this for you to see the result. docs.google.com/spreadsheets/d/1kMYspnz_H9QLQIJkOVXdsPn0H53ljsX9lYHPEn8eBoo/edit#gid=553006941&range=A1 You can easily hide it by toggling the Group tag on the left. Don't forget to go to: File > Make a Copy for your own versions to play with.
Now there is a Edit Button in drop down list which is creating problem to Owner. If owner creates certain options in dropdown and share it with other to fill the information just by selection. But EDIT BUTTON allow editors to edit the dropdown list. any suggestions
Unfortunately, the best you could do here is to protect the range of the source sheet tab. Editors will still be able to click the edit icon and change the rain with is a pain. I don't think the new chip driven dropdown menu set up was well thought out on this one. :(
Yeah, that is definitely one of the limitations of this approach. My recommendation would be to create an Apps Script dialogue box to insert items for more complex options, thought the new LAMBDA function might help you out too.
Question... Is it possible: I am doing a stock sheet, Names of companys are in cell A, ticker in B. The Following 170 cells are company data. I have made a droppdown menu choosing ticker and the company names, ticker ect comes opp to a bigger window with the current 12 month graph. This info is from googlefinance. BUT i would love to have like: PS, PB and other numbers popping up to that i can not get from google finance. Like i want Teslas P/B, Tesla is on line A27, ticker on B27 and say PB on X27. Is there a way to say if ticker is TSLA get infro from ""=X27?
Yes. It's possible you might want to look at IMPORTHTML for this to draw from a custom data source. Alternatively the team from Set and Forget have a great produce to help out with importing trickier data.
You can add multiple ranges to the list in the same manner that column B was built. It will increase in complexity exponentially as you add a new column. You would have to transpose an option list for each extra column in the 'Notes' sheet tab.
Hi Sean. You won't be able to do it infinitely unlike how you can leave formulas open (e.g. A1:A). The best you can do is select the column, 'crl + c' to copy then select the range of columns by select the first one and holing shift down to the last one and then 'right-click' select 'Paste special' > 'Data validation only'. If you want to apply the data validation to a new column that is created, you could record a macro or you could dive into Google Apps Script and use the onChange() custom trigger to automatically add data validation to newly created columns.
@@yagisanatode awesome dude. Thanks so much for getting back to me so quickly. Really appreciate it 🙏. I'm setting up a lead generation prospecting tracking sheet, which is designed to tell me what prospects have received certain messages I've sent them and whether or not they are interested in my offer. Lets say I have 90 columns that all have the same data validation setup and each column is related to a date (added in a row above the data validation columns). As I move across each column, updating points of contact and the status of each lead (currently 100s of leads being recorded in individual rows), how can I setup a column where it automatically updates the various points of contact and status of each lead per row? With so much data being recorded, this would be so great for better tracking the journey of each lead. I hope I'm making sense. 🙂 I can show you a specific time of a youtube video where a very quick example of this is shown. I just don't know how the publisher did it.
@@yagisanatode Happy new year! :) I hope you're doing safe and well. Just in case you want to see an example of what I was talking about on how to update cells based off of data validation dropdowns, here is what I am talking about (check out this clip for 30 seconds) - ruclips.net/video/U8ISI7M6l1g/видео.html When he selects an option from any dropdown from Column F and beyond, you'll notice column E is updated. I really want to know how to do that :)
@Sean Byrne Yes it looks like a there are a few things going on in their script. It seems like the author is sending emails and follow up emails using the onEdit() Google Apps Script trigger and then updating relevant cells. I'm fully booked with client work at the moment, but I do have a hand picked team of Google Apps Script / Google Sheets freelance developers you could reach out to here: yagisanatode.goodgig.work/ or you could reach out to Serge Gatari, the author of the video and see if they are willing to sell you the sheet and accompanying script or make tweaks as you require. I hope this help. ~Yagi
Hello! I am making a spreadsheet kind of like the one that you are doing in the video except mine is for dog shows and the groups that the breeds are in for the dog shows, like for example there is a Herding group and there are 31 breeds in that group and I need to make a drop-down list so when I select the group Herding in Column A the drop-down list in column B will be all the breeds in that Group, The way that I have it set up currently is that I have Group in A1 and Breed in B1, From A3-A33 I did a data Validation with Item list with the names of the 7 groups as choices in the drop-down list, Now I need to do the same in Column B3:B33 to where when I select let's say Working Group in Column A3 and I go to the drop-down list in Column B3 I would like it to change to the breed list for that specific group. Kind of like when you go on a car dealership's website and select the make say like Chrysler and when you click Model it has all the models made by Chrysler so on and so forth. Thank you for your help in advance.
In your 'Notes' tab in Col A You would list each herding the group 'n' times where 'n' is the total number of breeds for that group. Then in column B you would list each breed. You can then reference this to reduce down your Col B drop down. Hopefully this helps.
Hi Hubert, you're right. This solution does not cover all usages, particularly datasets with many dependent dropdowns. The most success, I have had I these circumstances is to rely on an input dialogue or sidebar using some Google Apps Script. Hope this point you in the right direction to search and best of luck with your project.
At this point, I've practically seen every single video on a multi-row dependent drop-down list, but this is the easiest and fastest way to do it. You, my friend, are a life saver!
Glad it helped!
For a long, I am searching for this solution and tried many videos, to do the same, but could not get success. As most of the tutorials are not so clear. But this is very easy and simple to understand. I really appreciate your efforts to make this tutorial simple to understand.
Thank you very much and keep it up.
Great to hear. Glad you found it useful.
Succinctly explained, i've looked for a simple solution to this for a while and this one is by far the best. Thank you!
Thanks for the kind words.
I've been looking for a solution for days and this tutorial saved me!! Thank you!
This wasn't the first video I saw that used the transpose thing but you explained it much better.
Great to hear you found the tutorial helpful.
This is what I was looking for so many days. Thanks a lot for the this video.
Glad it was helpful!
Very helpful tutorial. One thing that your video did not discuss though is that on your "Notes" tab column E has to be copied (I did past 100 to test). It will show blank (as discussed) until information is put into your "Main" tab (such as A60). Then the information on your "Notes" tab will have info.
Thanks for the feedback.
Now I can impress my boss! 😆
Very helpful. Thank you so much! 🥰
I subscribed to your channel because of this. 😊
Really made it simple and easy to understand. Only one thing I would like to know is if I have selected value from drop down in B2 and then I change the value of A2, it shows invalid cell but does not clear it. Is there any way to clear the cell as I am using the combination of 2 values to get final result. Be my lifesaver too ;)
Hi chirag110, unfortunately your only way of doing this would be with a little Google Apps Script code with the onEdit() function trigger.
Basically you want to listen for changes to col A and then use offset to change the corresponding cell in Col B.
Similar to this approach for adding static date time stamps: yagisanatode.com/2018/02/21/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/
You are a beautiful human bei
ng thank you soooo much. Just made life so much easier for me!!!
You're very welcome, Liam. Glad it helped.
Thank you so much for this amazing tutorial, very helpful!
Just wondering if I can apply the same formula to the next column. E.g. After I select the cell on column B from the drop down list, then on column C, it will appear another filtered drop down list.
Yes, you can. You will need to extend the Notes sheet to handle this, but it will work. Once to get to a large number of dependent dropdowns, I recommend switching to Google Apps Script to either do this onEdit() or with a Sidebar or Dialog box input.
Superb! It really works for me to create 2 dependencies drop down. Thanks
Great to hear!
Awesome! super simple example thanks!
Great to hear!
Thank you, bro!!!
Helped a lot
Glad it helped!
This was very helpful. Thank you
You're welcome, Tiff.
Thanks. such a knowledgeable things you tell.
Glad it was helpful!
Love the video and how you explain everything. Maybe I missed this. on my main tab - I have category (A) and subcategory (B). I can drag the categories all the way down.. no problem. but can't get the subcategories to populate all the way down the page. Any thoughts. Thank you.
Hi Pam make sure you update your notes range too. It's one of pitfall of this approach.
Thank you for this, well explained
You're welcome. Thanks for the feedback.
Hi there, just want to thank you for the video! I have a very similar setup I'm trying to implement in our Wholesale Order sheet. Currently everything works, except for when an order is complete and it's time to remove it from the list; the only way I've found that doesn't break the dynamic dropdown is to copy ALL the data beneath the 'obsolete' order, paste it on top of the old order, and then manually remove whatever was left at the bottom that is now duplicate. Is there a way to delete rows mid-sheet without wreaking havoc on the cell references? I'm not actually using FILTER, just ArrayFormula, TRANSPOSE and a whole lot of nested IF statements (I modified from another tutorial I'd found). Is there a way to use Scripts instead to, on delete of a row, force the dynamic data validation references to shift up relatively?
I don't have a non Apps Script solution that comes to mind. Perhaps an ARRAYFORMULA on the dropdown options. If I get a chance I will take a deeper look.
@@yagisanatode Hey, thank you for the response! No worries, I had figured it would need to be Script - I will look around for some solution there. I've successfully used scripts in the past to automate things like email responses, but haven't had success yet with the more SQL-esque behaviors I need. I will keep trying!
Informative!
Thank you, this helped me a lot!
Glad it helped!
Thank you ,. you have saved me !
You're welcome.
Thank you for this!
Your welcome.
Im doing a yearly production record. I can (and have) made a dependent drop down with the first column representing our brands and the second representing individual products that brand offers. Im trying to make each months production (aka one sheet) have the dependent drop downs. Does that mean i need to create a cell reference for each sheet?! Or is there a way to have the dependent drop downs show on each sheet with just the one "master" set of cell references?
Im also aware this may be an app script thing but im hoping not...
You are such a life saviour for me😘😘. I am going to subscribe you now.
Thanks for subbing!
I have a question.
I am trying to make multiple dropdowns of the same list of items, but I want that list to exhaust itself after all the dropdown options have been chosen. Here's what I mean:
I have 4 dropdown lists containing 4 options each. In dropdown list #1, I have options A, B, C, and D
In dropdown #1, if I select option D, dropdown #2 will exclude option D; only options A to C will be available.
Now, in dropdown 2, if I select option A, dropdown #3 will only contain options B and C.
That's what I am trying to do. So how do I perform this set of operations?
If I continue on with that pattern and select option C, then dropdown #4 will only have one available option which is B.
For clarity, the reason A, C, and D are not available in dropdown #4 is because they were already selected in dropdowns #1, #2, and #3. Thank you for your time.
Hi MasterOFSuperFunny, Yes. I provides some bonus information in my written version of this tutorial to answer your questions along with an accompanying example Google Sheet that you can find here:
yagisanatode.com/2021/07/04/update-a-range-of-dropdown-lists-in-a-google-sheet-dynamically-based-on-a-previous-dropdown-choice/#Bonus_1_-_A_dropdown_list_family_with_options_that_are_removed_after_each_selection
Here is the new video version released today! ruclips.net/video/iJBovpTP8J4/видео.html
How do you maintain the integrity of choices when you sort your Option 1 column? (Or sort all your data by a different column)? All the validation doesn't stay aligned when the data is sorted.
Hi TDW Box Office. A good option her is to use the filter tool.
ruclips.net/video/fghVqHu9qdI/видео.html
Or you could use some data validation. Alternatively, ensure that you select the entire range of the data before filtering it.
Pure gold, Thanks for share
Glad you enjoyed it
THANK YOU THANK YOU THANK YOU
thanks so much!!! very useful
You're welcome!
Wonderful :-) ! Such a helpful video. many thanks!
You're very welcome!
Nice tutorial. Cant i add another layer of selection using same method? Lets say one-A-1 based on your example
Yes you can!
Heartily Thanks ❤
Welcome 😊
PERFECT!!! Thank you!
Glad it helped!
You are only solution for for this
Thanks👍👍👍
That's great to hear. Thanks.
I am yet to find out how to make a entry row that gets the information put in first six cells postponed one or two rows below as soon as I change the status of the cell six of that first row. The idea is to keep the most recent data on top automatically and keep using first row as a data entry row. Not sure if it can be done
When using filter by A-Z or Z-A, how to solve the non dynamic range problem in the data validation ? Thanks!
I have this same question
Hi Liyun Jin, Yes, this approach is not ideal when combined with using filter. The better solution would be to use come Google Apps Script to provide options. However, if you don't want to go this approach. You could reference the range in another Google Sheet tab and apply a SORT function based an a sort dropdown. I've added an extra tab in the Google Sheet tutorial for this for you to see the result.
docs.google.com/spreadsheets/d/1kMYspnz_H9QLQIJkOVXdsPn0H53ljsX9lYHPEn8eBoo/edit#gid=553006941&range=A1
You can easily hide it by toggling the Group tag on the left.
Don't forget to go to: File > Make a Copy for your own versions to play with.
Help me me a lot thank you!!!
Glad to hear that!
Now there is a Edit Button in drop down list which is creating problem to Owner. If owner creates certain options in dropdown and share it with other to fill the information just by selection. But EDIT BUTTON allow editors to edit the dropdown list. any suggestions
Unfortunately, the best you could do here is to protect the range of the source sheet tab. Editors will still be able to click the edit icon and change the rain with is a pain. I don't think the new chip driven dropdown menu set up was well thought out on this one. :(
@@yagisanatode I have reported this issue to google team also. Lets see what they can do
If you insert or move rows in your main sheet, it causes problems. Do you have a way to fix this?
Yeah, that is definitely one of the limitations of this approach. My recommendation would be to create an Apps Script dialogue box to insert items for more complex options, thought the new LAMBDA function might help you out too.
How about if if you add a separete options for one-A and so on?
Question... Is it possible: I am doing a stock sheet, Names of companys are in cell A, ticker in B. The Following 170 cells are company data. I have made a droppdown menu choosing ticker and the company names, ticker ect comes opp to a bigger window with the current 12 month graph. This info is from googlefinance. BUT i would love to have like: PS, PB and other numbers popping up to that i can not get from google finance. Like i want Teslas P/B, Tesla is on line A27, ticker on B27 and say PB on X27. Is there a way to say if ticker is TSLA get infro from ""=X27?
Yes. It's possible you might want to look at IMPORTHTML for this to draw from a custom data source. Alternatively the team from Set and Forget have a great produce to help out with importing trickier data.
Thanku 😊 so much sir ❤️
Thanks a lot. This is what Exactly I Want.....
Great to hear.
life saver...many thank
No worries. Glad you found it useful.
Can you only attach one range or multiple?
You can add multiple ranges to the list in the same manner that column B was built. It will increase in complexity exponentially as you add a new column. You would have to transpose an option list for each extra column in the 'Notes' sheet tab.
Can anyone tell me how to copy a data validation column across infinite columns that go in the direction of the right of the page?
Hi Sean. You won't be able to do it infinitely unlike how you can leave formulas open (e.g. A1:A). The best you can do is select the column, 'crl + c' to copy then select the range of columns by select the first one and holing shift down to the last one and then 'right-click' select 'Paste special' > 'Data validation only'.
If you want to apply the data validation to a new column that is created, you could record a macro or you could dive into Google Apps Script and use the onChange() custom trigger to automatically add data validation to newly created columns.
@@yagisanatode awesome dude. Thanks so much for getting back to me so quickly. Really appreciate it 🙏. I'm setting up a lead generation prospecting tracking sheet, which is designed to tell me what prospects have received certain messages I've sent them and whether or not they are interested in my offer. Lets say I have 90 columns that all have the same data validation setup and each column is related to a date (added in a row above the data validation columns). As I move across each column, updating points of contact and the status of each lead (currently 100s of leads being recorded in individual rows), how can I setup a column where it automatically updates the various points of contact and status of each lead per row? With so much data being recorded, this would be so great for better tracking the journey of each lead.
I hope I'm making sense. 🙂 I can show you a specific time of a youtube video where a very quick example of this is shown. I just don't know how the publisher did it.
@@yagisanatode Happy new year! :) I hope you're doing safe and well. Just in case you want to see an example of what I was talking about on how to update cells based off of data validation dropdowns, here is what I am talking about (check out this clip for 30 seconds) - ruclips.net/video/U8ISI7M6l1g/видео.html
When he selects an option from any dropdown from Column F and beyond, you'll notice column E is updated. I really want to know how to do that :)
@Sean Byrne Yes it looks like a there are a few things going on in their script. It seems like the author is sending emails and follow up emails using the onEdit() Google Apps Script trigger and then updating relevant cells.
I'm fully booked with client work at the moment, but I do have a hand picked team of Google Apps Script / Google Sheets freelance developers you could reach out to here: yagisanatode.goodgig.work/ or you could reach out to Serge Gatari, the author of the video and see if they are willing to sell you the sheet and accompanying script or make tweaks as you require.
I hope this help.
~Yagi
Hello! I am making a spreadsheet kind of like the one that you are doing in the video except mine is for dog shows and the groups that the breeds are in for the dog shows, like for example there is a Herding group and there are 31 breeds in that group and I need to make a drop-down list so when I select the group Herding in Column A the drop-down list in column B will be all the breeds in that Group, The way that I have it set up currently is that I have Group in A1 and Breed in B1, From A3-A33 I did a data Validation with Item list with the names of the 7 groups as choices in the drop-down list, Now I need to do the same in Column B3:B33 to where when I select let's say Working Group in Column A3 and I go to the drop-down list in Column B3 I would like it to change to the breed list for that specific group. Kind of like when you go on a car dealership's website and select the make say like Chrysler and when you click Model it has all the models made by Chrysler so on and so forth. Thank you for your help in advance.
In your 'Notes' tab in Col A You would list each herding the group 'n' times where 'n' is the total number of breeds for that group. Then in column B you would list each breed. You can then reference this to reduce down your Col B drop down. Hopefully this helps.
I need to create dynamic dropdown list for thousands of cells, this solution is not good enough :(
Hi Hubert, you're right. This solution does not cover all usages, particularly datasets with many dependent dropdowns. The most success, I have had I these circumstances is to rely on an input dialogue or sidebar using some Google Apps Script. Hope this point you in the right direction to search and best of luck with your project.
1