Google Sheets Secret: Effortless Dynamic Dropdown Trick! No Scripting Required!
HTML-код
- Опубликовано: 7 авг 2024
- Check out this dependent dropdown hack I discovered in Google Sheets to be able to quickly and easily add multiple dependent dropdowns with NO scripting required.
You can make a copy of the spreadsheet that you can play around with using this link: bit.ly/3DjzYiG
If you've never used dropdowns in Google Sheets, check out my dropdowns tutorial here: • Video
If you've never used cell reference locking (for example, the $1:$1 reference you see in the video), check out my cell reference locking video here: • Cell Reference Locking...
To recap, here's what you need to do for the simple version in the Simple Version tab:
1) Create a normal drop-down in Column A from the first column in the simpleData tab.
2) Create a transposed filter function in the simpleDropdown helper tab that will return the data from Column B in the simpleData tab for each row in Column A (starting in A2, then hover over the blue box and click and drag down to copy the formula down).
3) Create the hacked drop-down in Column B from the first row in the simpleDropdown helper tab for the 2nd row in Column B on the Simple Version tab.
4) Update the data validation to delete the "$"s so the row reference is not locked.
5) Select B2, then click and drag on the blue box on the bottom right to copy the data validation down.
![List Tab Names in Google Sheets Using a Custom Function [SheetsNinja]](http://i.ytimg.com/vi/BVLgk2LkCpE/mqdefault.jpg)
![List Tab Names in Google Sheets Using a Custom Function [SheetsNinja]](/img/tr.png)
It's crazy how well this video is made and how helpful this was for what I'm doing. I'm really hopeful about the layout of my next sheet/project after being able to implement this easily!
So glad this was helpful for you! All the best on your next project!
the best tutorial of Dependent Dropdown! thanks!
Glad this was helpful!
Much simpler than other video, great video. Wish you had more subs
Awesome, glad this was helpful!
Very Good! It really help me a lot! Thank you so much!
Awesome, glad it helped!
Great tutorial! Thank you very much! Big help!
Awesome, glad it was helpful!
lifesaver, thank you so much!
You're welcome!
Thanks! Super helpful!
Awesome, glad to help!
Great Tutorial and it helped a lot. I have a request when i delete the data in column A, B column is not cleared automatically. Do you have a way to improve it? thanks.
The only way you can automatically clear cells would be using the scripted version of this. I have a tutorial on that here: ruclips.net/video/5Yysv-QouTQ/видео.html
Hey there,
On Limitations you say exactly my case: When I use sorting, it breaks evrything.
Can you point me the video you mentioned??
Thanks!
Here's the video: ruclips.net/video/5Yysv-QouTQ/видео.html
Thanks!
Absolutely! Glad this was helpful for you! Have a great day!
Your video is great, and you explain every step very well! I am having a problem making this work. I am using a MacBook Air M2, running Sonoma and I am using the Safari Browser. I have used Google sheets for some time, even back when dependent data validation was a different setup, and I used indirect function. Currently, when I try to duplicate your exact steps, when I set the =filter, I get an error saying Filter has mismatched range sizes. Expected row count: 93. Column count: 1. Actual row count: 1 column count: 1
No matter how many rows each sheet has, I get similar errors. Any suggestions? Thank you in advance.
Can you paste an example of your filter formula? Typically mismatched range sizes will be when you do something like this:
=filter(Sheet1!A1:A10,Sheet1!B1:B20=Sheet2!B3)
In this case, the two Sheet1 references don't have the same number of rows in the formula.
=filter(SimpleData!B:B,SimpleData!A:A,SimpleVersion!A2)
You have a comma instead of equals at the end. It needs to be:
=filter(SimpleData!B:B,SimpleData!A:A=SimpleVersion!A2)
@@SheetsNinja Thank you, I feel so stupid!
If you feel stupid, it means you're learning! I've felt stupid so many times over the years in Google Sheets, but science shows that when we struggle more to learn something, it actually helps our brain to retain the knowledge better. So good job pressing through!
after
1000 rows is the formula will be carried?
If you have more than 1000 rows in the original tab, you will need to have the same number of rows in the drop-down formula tab and drag down the formula