Thank you very much for this video! One question though - what if you need to name the range with a name that has spaces in it, like say the name of a US State like "New York?" 'Cause mine says invalid name.
I'm having the same issue. I can't put spaces or punctuation in the Named range. I haven't figured out how to get around this. The example in the video he is only using single words, but I have much more complex names in my spreadsheet!
Amazing tutorial thank you. Just took a workload off one's shoulder with these nifty tips. I especially liked the "define named range". While struggling to get a function to work, it's really tedious having to mark up the ranges again and again.
Excellent, echoing Mr. Parsons, you have that knack for speaking off the cuff with very few errors. Could you bottle that ? You could retire! I had no inkling that I could make a validation range from a ROW, I've always assumed COL only. Video would have been worth it just for that
In excel we can do it easily, if I have a column full of drop down how to do it ? In excel we enter it (Indirect formula) directly in data validation bar, how to do the same in google docs?
Works great for one row. But what if you have a list of people, say karate students, each with a pull-down for rank (beg, int, adv, black) and a contingent weight division indirectly linked to rank (light, light-middle, middle, middle-heavy, heavy). Do I have to construct this for every student? I tried using the indirect function in the data validation window and I get an error.
@7:40 MY MAN! Thinking ahead and creating solutions for upscaleability (if there is such a word). Great tutorial and I am VERY grateful for it. Thank you
WONDERFUL CHANNEL! i wish i saw this earlier. do you have a video explain in short the differenct formulas? (example IF is.... SUMIF is.... IMPORT ..LINKING... QUERY ) trying to go thru all your videos to see what is relevant for my work needs. thank you again for teaching !
Very nice tutorials! I've learned so much from you! But I have a question if you don't mind: If you continue down the A column with brands, how can you make the dropdown in B column continue down as well and be dynamic as it is in the video? I mean, lets say A3 is BMW, A4 is Audi, etc. how can the B3, B4 update as well and all this to continue down the columns? Thanks!
This was so helpful thank you! Could you direct me to a way to duplicate the function of the cells drop-down lists to multiple cells? or do they need to be done individual rows as show above?
Very cool. But, I would like to be able to have the dynamic lookup for an entire column. This works great if you are only doing it for 1 cell. I want to have two columns where the values for cells in the first column determine the choices for the values in the second column.
Hi there, very helpful video. Is there anyway you can do the indirect function for multiple cells simultaneously or you can do it only one by one? thanks anyway
Great video, but is there a way for a user to add to a list from the dropdown list. For example if the user wanted to add a new make of car, say Ford, the user can just type it in from the dropdown list and it will be added to the list?
I think you need to learn about arrayformula method and filter function. Filter(array,criteria) If you need 3 level. That means, the first input you chose will be the criteria for the second input. The second input will be criteria for the third input. And so on.
How I can do it for all lines in column, I tried this way and its always provide options from first cell. Like if BMW in first row and Toyota in second, still on second row I am getting BMW models
You missed a trick with naming the ranges. You could have also listed the range to be C3:C. This would have made the range go on and on to the very bottom. Same works from C1:1. Otherwise this really worked to demonstrate this.
Nice content! thanks! And if in tab "main" you have other rows with dropdown lists? It's way more common to have this scenario than just have one row with it. The way it is if you copy the validation date cells and you choose another option it won't work because you have already an information "filtered" in the "lists" tab.
After you select the model, would you be able to populate some cells to the right to display packages available, without them being in a drop-down? So after you choose Toyota and Corolla, could you (to the right) display: [L] [LE] [SE] etc? Brackets denote a separate cell.
Thanks sir...your video gave me inspiration. Finally this is the code I wanted: =FLATTEN (INDIRECT (F12),"") A cell referenced to a dropdown which is inside the dropdown is my ranged name. But I want convert it from horizontal to vertical. So I use FLATTEN funtion.
Fantastic, I’d really like to take it a step further and have another column that was colour. But the name range seem to conflict. Can you advise on this please
what you in Main you have multiple entries of Make. e.g. A2- Toyota, A3-Mercedece , then in lists sheet do i need to duplicate that dynamic generated values. i wanted to create an expense tracker with expense category and subcategory. although on google sheet not able to use INDIRECT function in the datavalidation LIST, it works on MS excel. e.g. in data validation list range =INDIRECT(D2)
Thank You! That's a great explanation! Do You have any suggestions how to get "Model" selection blank immediately if the "Make" is changed? Like in 14:16 on video if BMW is changed to Toyota and M3 is not a valid model anymore. What are the ways to clear that model cell automatically? Thank You in advance!
@@ExcelGoogleSheets If I want to create a series of such dependent dropdown lists like this, how can I do it? I can not drag it like usual right? What should I do? Can you show me how?
Wow. This is very helpful. Very much what I was looking for. However, how do I go about replicating that row down till the last row? Considering that my data is a different or very row has a different manufacturer and make? Will all the formula in this tutorial hold good even then? When replicated!? A response would be greatly appreciated :)
When I auto fill the rest downward. It would give me like vegetable = mango when it should be vegetable = Broccoli. I think the indirect isn’t working when I autofill
This is great, but can I do the options with multiple sheets? Like if cells B2 is "Alfa" > C2 will gave filtered list on sheets called "Alfa" and so on,
Hi! Thanks for your detailed explanation ;) But don't you know how to make the same for 10 rows with the selection? For instance, if we have to choose 10 cars and models in the spreadsheet? Thanks in advance!
How do you deal with this if the car brand was 2 words? Because the named ranges cannot have spaces and I want the dropdowns to have proper spacing. For example Alfa Romeo...how would that work?
I have a spreadsheet which has my leads data from Google Ads based on month, date, name, phone, email & country. I want to have three options to filter this data. First month wise data only, second country wise data only & third both month and country wise data together. Please help me with the condition format that needs to be written. Appreciate your support. Thanks!
Nice one! But how about if you put Aston Martin in as a Make? Named Ranges doesn't allow spaces if I'm not misstaken. Is there a workaround for that scenario? I know this is an old video so I can only hope for an answer to this. Couldn't figure it out my self unfortunately.
Is there a way to do the dropdown on every row? which will depend on the value beside it. For example column A will hold the first dropdown and B dropdown will depend on column A.
is it possible to eliminate an option from a dropdown after it is selected, I have several drop down lists, and I want to make sure I can't select the same item multiple times from a single list.
Great video, thought I understood everything you discussed on the video. However, I have a checkbook register on Google sheets with about 4500 rows. I have made the “Named Ranges” on a separate sheet from the actual check register sheet. Then I made the “Indirect Function” on the Data2 sheet, but am having trouble applying it to my register. If you could help please, I could send you a file with Old Data I do not care about. I would appreciate your assistance.
How can you make it work for a long list of rows?? When I set up another row under Toyota, the main drop down does not auto-populate to the next dependent submenu.
Perfect, no music, clear speaking and intelligent! Click here to send him a new BMW
the best video to show the easiest way to set Downlist as so far, no need any edit any sourcecode.
this is the most useful youtube channel ever
Great tip about leaving room in your range to add items in the future since blanks won't be included in the drop down.
Thank you very much for this video! One question though - what if you need to name the range with a name that has spaces in it, like say the name of a US State like "New York?" 'Cause mine says invalid name.
I'm having the same issue. I can't put spaces or punctuation in the Named range. I haven't figured out how to get around this. The example in the video he is only using single words, but I have much more complex names in my spreadsheet!
It can get quite complex, but it's very useful, and your tutorial was great. Thank you.
Works perfect for row 2, but what if I need data validation for multiple rows?
ua-cam.com/video/s-I8Z4nTDak/v-deo.html
Is this answered
Amazing tutorial thank you. Just took a workload off one's shoulder with these nifty tips. I especially liked the "define named range". While struggling to get a function to work, it's really tedious having to mark up the ranges again and again.
I have often use indirect, this is a really powerful function. Nice content !
Excellent, echoing Mr. Parsons, you have that knack for speaking off the cuff with very few errors. Could you bottle that ? You could retire! I had no inkling that I could make a validation range from a ROW, I've always assumed COL only. Video would have been worth it just for that
Ages later, this is still helpful! ty needed something like this.
Great presentation skills and teaching habits
Wish I found this back in 2017! Thanks for putting it together!
Excellent presentation. Did a lot of pausing and rewinding. But, finally mastered the concept. Works perfect!
INDIRECT: Awesome, I've needed that function so many times!
Wonderful. Is it possible to extend to more than two independent drop down lists?
Unbelievably helpful
In excel we can do it easily, if I have a column full of drop down how to do it ? In excel we enter it (Indirect formula) directly in data validation bar, how to do the same in google docs?
Do you how to? I'm facing this issue rn
Works great for one row. But what if you have a list of people, say karate students, each with a pull-down for rank (beg, int, adv, black) and a contingent weight division indirectly linked to rank (light, light-middle, middle, middle-heavy, heavy). Do I have to construct this for every student? I tried using the indirect function in the data validation window and I get an error.
Try this with FILTER function, when you understand how to do it you'll absolutely love it)) Thanks for the video
thank you very much, very easy to understand, you are a very good teacher. many thanks
@7:40 MY MAN! Thinking ahead and creating solutions for upscaleability (if there is such a word). Great tutorial and I am VERY grateful for it. Thank you
WONDERFUL CHANNEL! i wish i saw this earlier. do you have a video explain in short the differenct formulas? (example IF is.... SUMIF is.... IMPORT ..LINKING... QUERY ) trying to go thru all your videos to see what is relevant for my work needs. thank you again for teaching !
Can the data validation extend to more rows? Row 3, column B is pulling from what I input in row 2 column A.
same question here, hope to get an answer.
@@aimeo.saladaga2505 check link below, maybe it will help
Very nice tutorials! I've learned so much from you! But I have a question if you don't mind: If you continue down the A column with brands, how can you make the dropdown in B column continue down as well and be dynamic as it is in the video? I mean, lets say A3 is BMW, A4 is Audi, etc. how can the B3, B4 update as well and all this to continue down the columns? Thanks!
?
@@Sankofa906 what do you not get ? he just wants to have more dynamic dropdowns beneath the first one
Love your videos, they have helped me figured out so much! Thank you!
This was so helpful thank you! Could you direct me to a way to duplicate the function of the cells drop-down lists to multiple cells? or do they need to be done individual rows as show above?
I guess the author does not know how!!! without this function his advice is not very helpful
Impressive. The tutorial is very easy to understand. Thank you.
Very cool. But, I would like to be able to have the dynamic lookup for an entire column. This works great if you are only doing it for 1 cell. I want to have two columns where the values for cells in the first column determine the choices for the values in the second column.
hi did you got answer for this..?
same doubt for me also
This is actually clever! Love it
Just what i was looking for straight to the point, would give two thumbs up if i could thanks
Amazing tutorial, very detailed as usual, thank you !
Thanks. Very helpful. How to do the same dropdown list several times in the same google sheet? Thanks.
Amazing!! Just what i was looking for. Thanks for sharing!
Great to hear!
Many Thx for the knowledge man u deserve more views and sub.. sadly alot of ppl love to watch compilation tiktok videos. Keep doing this man
That's really an awesome tutorial, brother. Thanks a lot for sharing this with us.
Hi there, very helpful video. Is there anyway you can do the indirect function for multiple cells simultaneously or you can do it only one by one? thanks anyway
Great video...very easy to follow!
OMG this is what i need!! Thank you sm
!!
Thank you so much. This video helped me change the names everywhere but it worked. Thanks.
Great video, but is there a way for a user to add to a list from the dropdown list. For example if the user wanted to add a new make of car, say Ford, the user can just type it in from the dropdown list and it will be added to the list?
thanks a lot!!!!!!, know... how do I do the same fo multiple rows in the " main " tab?
I need help about these validation, what I do if I need that dependent validation for next few column also?
This is exactly what I am looking for too. Were you ever able to solve this?
I am also in need of this feature. In excel no problem... Any luck?
No, I still did not found solution for this except script based macro 😞
I think you need to learn about arrayformula method and filter function.
Filter(array,criteria)
If you need 3 level.
That means, the first input you chose will be the criteria for the second input.
The second input will be criteria for the third input. And so on.
Bless your soul. This is pure genius.
super good explained! thnks :)
How I can do it for all lines in column, I tried this way and its always provide options from first cell. Like if BMW in first row and Toyota in second, still on second row I am getting BMW models
Same qs
Now I can sleep! 😂 Very well taught. Thank you!
You missed a trick with naming the ranges. You could have also listed the range to be C3:C. This would have made the range go on and on to the very bottom. Same works from C1:1. Otherwise this really worked to demonstrate this.
Nice content! thanks!
And if in tab "main" you have other rows with dropdown lists? It's way more common to have this scenario than just have one row with it. The way it is if you copy the validation date cells and you choose another option it won't work because you have already an information "filtered" in the "lists" tab.
This is great! Very detailed explanation of the concept. Thank you :)
After you select the model, would you be able to populate some cells to the right to display packages available, without them being in a drop-down? So after you choose Toyota and Corolla, could you (to the right) display: [L] [LE] [SE] etc? Brackets denote a separate cell.
Thanks sir...your video gave me inspiration.
Finally this is the code I wanted:
=FLATTEN (INDIRECT (F12),"")
A cell referenced to a dropdown which is inside the dropdown is my ranged name. But I want convert it from horizontal to vertical. So I use FLATTEN funtion.
Now make second similar row
Fantastic, I’d really like to take it a step further and have another column that was colour. But the name range seem to conflict. Can you advise on this please
what you in Main you have multiple entries of Make. e.g. A2- Toyota, A3-Mercedece , then in lists sheet do i need to duplicate that dynamic generated values. i wanted to create an expense tracker with expense category and subcategory. although on google sheet not able to use INDIRECT function in the datavalidation LIST, it works on MS excel. e.g. in data validation list range =INDIRECT(D2)
This is one of the coolest things I know in Excel.
and how to copy those drop-down cell also for the whole row?
Thank You! That's a great explanation! Do You have any suggestions how to get "Model" selection blank immediately if the "Make" is changed? Like in 14:16 on video if BMW is changed to Toyota and M3 is not a valid model anymore. What are the ways to clear that model cell automatically? Thank You in advance!
Script.
Hi This is great! but if we need to use data validation for multiple rows and reference is only from one row?
How do i apply it to the rest of the cells on the main column?
Did you figure out this?
You have mentioned for only 1 cell as an example. what to do if we have 2nd row Main sheet A3 and A4 etc, how will it work?
This is so amazing! Thank you for sharing!!!!
Glad you enjoyed it!
@@ExcelGoogleSheets If I want to create a series of such dependent dropdown lists like this, how can I do it? I can not drag it like usual right? What should I do? Can you show me how?
Good video, well presented, thanks!
How can I change 3 dropdown lists simultaneously, based on 1 dropdown?
Great! Thank you very much!!
Thank you very much. I got what I expected. Keep posting
Brilliant, can we get the copy of the appscript from where we can copy it
Wow. This is very helpful. Very much what I was looking for.
However, how do I go about replicating that row down till the last row? Considering that my data is a different or very row has a different manufacturer and make? Will all the formula in this tutorial hold good even then? When replicated!?
A response would be greatly appreciated
:)
That is crazy useful - thank you!
When I auto fill the rest downward. It would give me like vegetable = mango when it should be vegetable = Broccoli. I think the indirect isn’t working when I autofill
You are a good teacher, thanks
Awesome. This video helps me. Thank you!
Thank you! This was so helpful!
Could you please make a video on how to perform the same operation in google form?
This is great, but can I do the options with multiple sheets? Like if cells B2 is "Alfa" > C2 will gave filtered list on sheets called "Alfa" and so on,
Amazing! Thank you so much!
Very useful, thank you!
And what can we do for multiple rows with same drop down button like for countery and then district
Hey every thing is good but should we do this to each cell for data validation, if we need this kind of dropdown list in whole sheet?
I love you this is the best one
Hi! Thanks for your detailed explanation ;)
But don't you know how to make the same for 10 rows with the selection?
For instance, if we have to choose 10 cars and models in the spreadsheet?
Thanks in advance!
You'll have to use a script. I have multiple videos on the channel. Search "dependent drop down column"
How do you deal with this if the car brand was 2 words? Because the named ranges cannot have spaces and I want the dropdowns to have proper spacing. For example Alfa Romeo...how would that work?
thank you so much.
I have a spreadsheet which has my leads data from Google Ads based on month, date, name, phone, email & country. I want to have three options to filter this data. First month wise data only, second country wise data only & third both month and country wise data together. Please help me with the condition format that needs to be written. Appreciate your support. Thanks!
Nice one! But how about if you put Aston Martin in as a Make? Named Ranges doesn't allow spaces if I'm not misstaken. Is there a workaround for that scenario? I know this is an old video so I can only hope for an answer to this. Couldn't figure it out my self unfortunately.
Only way is to separate it with an underscore..so your column header will be Aston_Martin
Is there a way to do the dropdown on every row? which will depend on the value beside it. For example column A will hold the first dropdown and B dropdown will depend on column A.
Thank you sooo much, you safe my " ass" .....I just fixed my old Table which I completely forget how to arrange.
VERY HELPFUL! THANKS A LOT
A big thank you!
is it possible to eliminate an option from a dropdown after it is selected, I have several drop down lists, and I want to make sure I can't select the same item multiple times from a single list.
There is any way to copy the sheet with the indirect drop downs cells into a new spreadsheet?
Great video, thought I understood everything you discussed on the video. However, I have a checkbook register on Google sheets with about 4500 rows. I have made the “Named Ranges” on a separate sheet from the actual check register sheet. Then I made the “Indirect Function” on the Data2 sheet, but am having trouble applying it to my register. If you could help please, I could send you a file with Old Data I do not care about. I would appreciate your assistance.
Can this be done using a FILTER function? So that its more dynamic & doesnt need named ranges?
thank you very much, this is answering my question
How do you add that to multiple cells? If I want to add multiple makes per say
How can you make it work for a long list of rows?? When I set up another row under Toyota, the main drop down does not auto-populate to the next dependent submenu.
how about 3 level drop down? like Toyota -> Land cruiser -> AT or MT or like other
Great job I like your way to do that AWESOME
It is very basic option. What about a table with 1000 roles where each line has different make. Can this be done?
I am trying to add a column for my vinyl color selection. I want the ability to choose color and quantity.
Great video, thanks