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!
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: ua-cam.com/video/5Yysv-QouTQ/v-deo.html
If "Reject the input" is selected in the Advanced Options, the little red triangle appears in the corner of the column B cell if you change the data in column A. Doesn't clear it, but it does make it easier to see.
Any idea why, for the first transposed filter function, I might be getting this reference error? "Error Result was not automatically expanded, please insert more columns (101)." This is my formula: =TRANSPOSE (FILTER(Settings!B:B,Settings!A:A=Naming!A2)) Hoping its a user error that I'm totally overlooking
So if you have a lot of options, you could get that error because Google will automatically add more rows, but not always more columns. So in this case, you just need to add more columns to the right of your formulas in column A. If you select multiple columns (click on "B" for example, hold down shift and then click on "Z", then right-click and select "Insert __ Columns" (left or right), and do that until you have approx 101 more columns, then it should populate.
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.
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!
Unfortunately, you can't do this with the non-scripted method. You would have to set up a helper tab for each sheet you want this to work on. The other options are setting up the scripted version of this: ua-cam.com/video/5Yysv-QouTQ/v-deo.html Or a different version of this that does not require a dedicated helper tab: ua-cam.com/video/rFiFxoO407M/v-deo.html
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
Good video but why were you rushing through it? I know you have done this 100's of times, but the people watching your video haven't. Please slow down. Don't immediate switch screens when writing code. I barely saw that you added a bracket to the end of that first bit of code. 😳
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!
OMG!!! I've been watching different tutorial for 2hrs now and you make my life super easy!!!! This is what I need! wooha!!
Awesome, so glad this was helpful for you!
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 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: ua-cam.com/video/5Yysv-QouTQ/v-deo.html
If "Reject the input" is selected in the Advanced Options, the little red triangle appears in the corner of the column B cell if you change the data in column A. Doesn't clear it, but it does make it easier to see.
Any idea why, for the first transposed filter function, I might be getting this reference error? "Error
Result was not automatically expanded, please insert more columns (101)."
This is my formula: =TRANSPOSE (FILTER(Settings!B:B,Settings!A:A=Naming!A2))
Hoping its a user error that I'm totally overlooking
So if you have a lot of options, you could get that error because Google will automatically add more rows, but not always more columns. So in this case, you just need to add more columns to the right of your formulas in column A. If you select multiple columns (click on "B" for example, hold down shift and then click on "Z", then right-click and select "Insert __ Columns" (left or right), and do that until you have approx 101 more columns, then it should populate.
@@SheetsNinja YES that was exactly it. thank you!! not to be dramatic but this changed my life
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!
lifesaver, thank you so much!
You're welcome!
Hello, do you know how i can do this but the conditional dropdowns will copy over if i duplicate the sheet?
Unfortunately, you can't do this with the non-scripted method. You would have to set up a helper tab for each sheet you want this to work on. The other options are setting up the scripted version of this: ua-cam.com/video/5Yysv-QouTQ/v-deo.html
Or a different version of this that does not require a dedicated helper tab: ua-cam.com/video/rFiFxoO407M/v-deo.html
Great tutorial! Thank you very much! Big help!
Awesome, glad it was helpful!
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: ua-cam.com/video/5Yysv-QouTQ/v-deo.html
Thanks! Super helpful!
Awesome, glad to help!
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
Thanks!
Absolutely! Glad this was helpful for you! Have a great day!
Good video but why were you rushing through it? I know you have done this 100's of times, but the people watching your video haven't. Please slow down. Don't immediate switch screens when writing code. I barely saw that you added a bracket to the end of that first bit of code. 😳