Great video, but quick question. In your example when I copy the Make/Model down say 50 rows, the Make shows up, but the model says N/A. Any ideas on how to resolve that?
I want to create a list of items to control flow states. The cell needs to show an item that is dependent on another cell. That is, one of the items in the list needs to be automatically selected to match the state of another cell. How can I do something similar to this? I can explain better if you do not understand. I'm very happy that I have this opportunity on your channel, thanks for the help.
@@richardmhain I am not sure I understand. what do I need to make sure I can have the same effect in following rowst? so If I choose a Make in Main sheet A3, it will show the associated Models in B3. please help
@@avinaba if you have the dropdowns in A2 & B2 working properly, my guess is, it should work of you copy them to next rows. If not, them you might need to use absolute referencing to data validation, i.e. instead of A2:A, use $A$2:$A...
Thanks for the video, however I could not see this was working when I started populating the rows 2 onwards in "Main" Sheet. It was fine was for row 1 , but not the subsequent rows
Thanks for the video. Please help with this scenario. Let's say I have 2 Dropdowns: A1=Name and B1=Status. I want to prevent the user from choosing an option from "Status Dropdown" unless choose a name first from the "Name Dropdown". Is there a way to force users to do this? Thank you
Okay, that works fine. But that would only work for selection in row 2. Most users would like to continue down the rows. For example making a different selection in row 3 and so on. That is the biggest issue users want to overcome.
What if there are more than one rows needed in the "Main Sheet" , The match is checking with 'Main'!A2 only. What if it need to be implemented on other rows of Main sheet?
How can I make one cell change based on what I pick from the dropdown list on another cell? Can I make that change be an equation based on numbers in a 3rd cell?
Hi. Have been able to apply this to 1 row. How do I apply this to all rows in my sheet so that depending on selection in different rows the resultant dependant value should differ in each column
Thank you Sir for good tutorial, it's helpful for me, one thing could you please help me how to make drop down list to refer to sheet tab, let say we have sheet tabs as follow : Jan, Feb, March, April , etc , when drop down list click, it will open one of those tab, thank you
I need some help! How do you add descriptions to each item in a drop down list? For example: say I have a drop down lists of fruits. If I select “apple” I would like the cell underneath to say “an apple is a round red fruit”.
Wonderful solution! I beleive FILTER function works painlessly in this case. I am looking for a solution to implement dynamic dependent drop-down menu in google sheet for multiple rows without using app script. Maybe it can be done by using a combination of functions like: Filter, Match, Offset, Indirect
@@princesthaa Google Sheets - Dependent Dropdown List for Entire Column - App Scipt, Run On User Input - Part 1 video: ua-cam.com/video/1SIN5NyQ9fw/v-deo.html
great stuff, is there a way to make a list with dynamic dependent dropdown list. Example from A1:A20 in each cell you can select from a list of items, and B1:B20 dependent the value in column A the list in Column B will be acording.
How can we do this with the "Model" is in one column? For example: The Data list of "Make" and "Model" is only in two column? and the Result of the Dynamic DropDown is still the same as how you show?
And how can I solve it when I only use 1 Sheet? I have specified in e. g. A2 a Data Validation with the 2nd option and typed in all selectables which gives me a drop down. Based on the selection from A2 other Cells in the same sheet should be filled with the coorect values (hardcoded in the formular) but I can not find smth. like "SELECTEDDRPDOWN(sourcell, selected, output)". Is there any compareable function? I'll not have a "Tail-Qery" with doozens of functions or hughe additional script. I'll not have to add a place/2nd sheet like in this tutorial. I want it all in one sheet, over datavalidation and with a max. of 1-2 functions, not more and not less so I hope that this is possible with G-Sheets (at last ist must be possible otherwise G-Sheets falls back into the stone age)
Thanks for all the help! I’m pretty new to setting up databases in general. How are you locking the values? (Seems you’re inputting a keyboard shortcut then the $ shows up in the value)
Is this only possible with single rows or columns? I'm looking to do basically the same thing but instead of for instance your BMW row being 1 row, it would be 3 rows with the name on the middle row. ex: (It would say bmw at C2, C1&C3 would be blank and the data will be range D1:I3) I'm basically looking for a version of this that allows block ranges instead of row/column only ranges. ex: (C1:C for names, D1:I4 for data)
This Is a great trick. I have a different problem. I have Data validation for all rows. Is there any solution for that. For example Car model Data validation goes from A2:A1000. Can I Base my B2:B1000 data validation on the A column? Each row will be different.
If I'm file sharing with alot of people and I dont want to loose something that they just entered. Is there a way to automatically enter what's typed on one sheet by a person to another protected sheet where nothing can be deleted?
I appreciate your video tutorials. I've learned a lot. I just have question in relation to this video. How do we make the Main tab to become dynamic? Lets say we need to add another transaction on the list (Main tab), how do we expand the data validation all the way down? I'm looking forward for the update. I know you'll be able to solve it. You're such an awesome and smart guy! :)
i got this to work for one row but what i was looking to do is create a list of transactions so that each transaction i could record with this dependent drop down method, however it's all based on that one entry so how can i "copy/paste" or make that formula apply to the whole column so that i can have hundreds of transactions listed?
Hi , am looking similar formula could you help on sheet 1 column A (tv channels) will be like below: discovery hbo star discovery star hbo column b (program name)will be: man vs wild Jurassic park Oscar insect world Baywatch Spiderman column c (actor): James Alan sysan rajesh carolyn stev on sheet 2 On column A if i select discovery (tv channel) On column b need a dropdown man vs wild, insect word (program name list) and if i select insect word on column c it should display rajesh (actor)
hello , i`m trying to combine the "indirect" with "arrayformula" and it`s not working , if u can give it a look i`d be thankful , here is a link to a small version i made for sharing. docs.google.com/spreadsheets/d/1W7phC4MFG5cPhU5gxanY81tgsJAOOioU-EWP4h9nNxU/edit?usp=sharing
The problem is not indirect. The problem is the array formula fundamentally is flawed. Your VLOOKUP range will always stay the same range, it won't iterate though each option in the range.
the problem was that "indirect" can not work with "arrayformula" , so i summed up all my ranges in an array , and the "vlookup" searches for they key in all the ranges until if finds a match. problem solved , but i am not sure about speed difference if it searches in only one range or 12 ranges.
Glad you got it solved.Indirect has no issues with arrays it was just logically not working in VLOOKUP. If you search for VOOKUP(A1:A7,B1:B7,2,0) it will search VOOKUP(A1,B1:B7,2,0) , VOOKUP(A2,B1:B7,2,0) ,VOOKUP(A3,B1:B7,2,0) ,VOOKUP(A4,B1:B7,2,0) etc. It will not iterate through B1, B2, B3 separately.
Thank you for this! How do you get this to repeat on the main page? So every row has these same dropdown options?
Hey, can you please add links to the final sheets on your videos?
It can help a lot for those who learn by doing
This is way simpler than the way I was doing this! Thank you
Great video, but quick question. In your example when I copy the Make/Model down say 50 rows, the Make shows up, but the model says N/A. Any ideas on how to resolve that?
I want to create a list of items to control flow states. The cell needs to show an item that is dependent on another cell. That is, one of the items in the list needs to be automatically selected to match the state of another cell. How can I do something similar to this? I can explain better if you do not understand. I'm very happy that I have this opportunity on your channel, thanks for the help.
I don't understand the difference between what's int he video and your question. Maybe explain more?
This works well with one row only. What happens when I want the drop-down list repeated
same question me
Tried copying dropdowns in Main!A2:B2 to Main!A3:B999?
Let us know how it goes.
Thanks!
@@richardmhain I am not sure I understand. what do I need to make sure I can have the same effect in following rowst? so If I choose a Make in Main sheet A3, it will show the associated Models in B3. please help
@@avinaba if you have the dropdowns in A2 & B2 working properly, my guess is, it should work of you copy them to next rows. If not, them you might need to use absolute referencing to data validation, i.e. instead of A2:A, use $A$2:$A...
How we can apply it to the remaining rows?
Thanks for the video, however I could not see this was working when I started populating the rows 2 onwards in "Main" Sheet. It was fine was for row 1 , but not the subsequent rows
It’s great! But how can I to do it also on the Main sheet from A3 to A30 and from B3 cell to B30 cell and more? but not just A2cell and B2cell?
Thanks for the video. Please help with this scenario. Let's say I have 2 Dropdowns: A1=Name and B1=Status. I want to prevent the user from choosing an option from "Status Dropdown" unless choose a name first from the "Name Dropdown". Is there a way to force users to do this? Thank you
Okay, that works fine. But that would only work for selection in row 2. Most users would like to continue down the rows. For example making a different selection in row 3 and so on. That is the biggest issue users want to overcome.
Stay tuned, that will be coming soon.
visit below link may b your problem will be resolve..
ua-cam.com/video/srk_uoofNnU/v-deo.html
I have the same problem. What I want to do is Dropdown for "Make" - "Model" - "Year" how do i add more?
how to do in google sheet
@@ExcelGoogleSheets Can you advise if you have done a tutorial for multiple rows yet? thanks
What if there are more than one rows needed in the "Main Sheet" , The match is checking with 'Main'!A2 only. What if it need to be implemented on other rows of Main sheet?
Was looking for this solution since a long time!!!! Thanks a ton
Which video should i refer if i want to auto fill the cell B if i select dropdown in cell A???? So i dont need to choose anything on cell B
How can I make one cell change based on what I pick from the dropdown list on another cell? Can I make that change be an equation based on numbers in a 3rd cell?
Hi. Have been able to apply this to 1 row. How do I apply this to all rows in my sheet so that depending on selection in different rows the resultant dependant value should differ in each column
I am also looking for the same answer
Thank you Sir for good tutorial, it's helpful for me, one thing could you please help me how to make drop down list to refer to sheet tab, let say we have sheet tabs as follow : Jan, Feb, March, April , etc , when drop down list click, it will open one of those tab, thank you
I need some help! How do you add descriptions to each item in a drop down list? For example: say I have a drop down lists of fruits. If I select “apple” I would like the cell underneath to say “an apple is a round red fruit”.
check out VLOOKUP or INDEX/MATCH functions.
Thank you! That was amazingly helpful!!
How to make dropdown list from different workbook in google sheet?
How can we use importrange in data validation for dropdown list?
Wonderful solution! I beleive FILTER function works painlessly in this case.
I am looking for a solution to implement dynamic dependent drop-down menu in google sheet for multiple rows without using app script. Maybe it can be done by using a combination of functions like: Filter, Match, Offset, Indirect
I wasn't able to solve this without App Script. If you find a solution I'd like to see it myself. I do have a solution posted using Apps Script.
Where is the video for this solution? Thank you
@@princesthaa
Google Sheets - Dependent Dropdown List for Entire Column - App Scipt, Run On User Input - Part 1 video:
ua-cam.com/video/1SIN5NyQ9fw/v-deo.html
great stuff, is there a way to make a list with dynamic dependent dropdown list. Example from A1:A20 in each cell you can select from a list of items, and B1:B20 dependent the value in column A the list in Column B will be acording.
Search on the channel, I have videos doing this with a script.
@@ExcelGoogleSheets time to learn script. thx amazing work you have done with the channel.
How can we do this with the "Model" is in one column? For example: The Data list of "Make" and "Model" is only in two column? and the Result of the Dynamic DropDown is still the same as how you show?
Thank you! This tricks are so impressive and helpful!
And how can I solve it when I only use 1 Sheet? I have specified in e. g. A2 a Data Validation with the 2nd option and typed in all selectables which gives me a drop down. Based on the selection from A2 other Cells in the same sheet should be filled with the coorect values (hardcoded in the formular) but I can not find smth. like "SELECTEDDRPDOWN(sourcell, selected, output)". Is there any compareable function? I'll not have a "Tail-Qery" with doozens of functions or hughe additional script. I'll not have to add a place/2nd sheet like in this tutorial. I want it all in one sheet, over datavalidation and with a max. of 1-2 functions, not more and not less so I hope that this is possible with G-Sheets (at last ist must be possible otherwise G-Sheets falls back into the stone age)
Yayyyy I finally figured it out with YOUR help of course. Thank you. 😊😊😊
Thanks for all the help! I’m pretty new to setting up databases in general. How are you locking the values? (Seems you’re inputting a keyboard shortcut then the $ shows up in the value)
Click the reference in thr formula you want to lock and hit the f4 key
THANX
PS: 06:44 Use address() or cell() function to make better is possible?
Very helpful, thank you!
THIS works for first row only, I want to use it on every row, how can i use
Were you able to figure out how to use it for all the rows?
Is this only possible with single rows or columns?
I'm looking to do basically the same thing but instead of for instance your BMW row being 1 row, it would be 3 rows with the name on the middle row.
ex: (It would say bmw at C2, C1&C3 would be blank and the data will be range D1:I3)
I'm basically looking for a version of this that allows block ranges instead of row/column only ranges.
ex: (C1:C for names, D1:I4 for data)
I have several videos on the channel that do this with scripts.
This Is a great trick. I have a different problem. I have Data validation for all rows. Is there any solution for that. For example Car model Data validation goes from A2:A1000. Can I Base my B2:B1000 data validation on the A column? Each row will be different.
what if first data validation take based on column (vertical) not based on row(horizontally)?
If I'm file sharing with alot of people and I dont want to loose something that they just entered. Is there a way to automatically enter what's typed on one sheet by a person to another protected sheet where nothing can be deleted?
Great stuff, is there also a way to sort the drop down list alphabetical?
I think the sort function will help you with that
I appreciate your video tutorials. I've learned a lot. I just have question in relation to this video. How do we make the Main tab to become dynamic? Lets say we need to add another transaction on the list (Main tab), how do we expand the data validation all the way down? I'm looking forward for the update. I know you'll be able to solve it. You're such an awesome and smart guy! :)
I'm afraid you're going to have you use Apps Script for that.
Tried copying dropdowns in Main!A2:B2 to Main!A3:B999?
Let us know how it goes.
Thanks!
@@richardmhain I doesn't work by copying the cells. The dropdowns shows results based on what we selected on the first row only
@@amanagrawal6328 Do you have a solution for this yet? I'm having the same issue
How can I get this same functionality in web app?? I need that. Can you help please.
I want to make this drop down options available for more than one cell, how can we do that?
I have a couple of script solutions for multiple rows on the channel.
You're the best! Thank you
Before the "Indirect" everything is fine after this it doesn't work in my sheet
i got this to work for one row but what i was looking to do is create a list of transactions so that each transaction i could record with this dependent drop down method, however it's all based on that one entry so how can i "copy/paste" or make that formula apply to the whole column so that i can have hundreds of transactions listed?
Like you said "copy/paste", i.e. copy Main!A2:B2 and paste to A3:B999.
Please let us know how it goes...
Many Thanks!
Your videos are very helpful.
i Tried your method but instead of row data it shows column data please help
Thank you! Smart solution!
hi. its working only for one row. what about the remaining rows? if i bring it down its now working
You'll have to use a script ua-cam.com/video/s-I8Z4nTDak/v-deo.html
how can I do this in Excel please?
Hi , am looking similar formula could you help
on sheet 1
column A (tv channels) will be like below:
discovery
hbo
star
discovery
star
hbo
column b (program name)will be:
man vs wild
Jurassic park
Oscar
insect world
Baywatch
Spiderman
column c (actor):
James
Alan
sysan
rajesh
carolyn
stev
on sheet 2
On column A if i select discovery (tv channel)
On column b need a dropdown man vs wild, insect word (program name list)
and if i select insect word
on column c it should display rajesh (actor)
Please someone share the sheet containing the formula in working condition.
Excellent tutorial.
If it have 3 level ,how to use
There is a quicker way and make everything Dynamic, Just use the filter formula. I have been doing it this way for a long time.
I have a video doing this with filter function too ua-cam.com/video/j_0z4FReN5A/v-deo.html
So we have the following for dynamic dropdown:
. Named Ranges
. Match
. Filter
Got the following versions too?
. Script
. SQL
Thanks for sharing...
It does not work with the remaining cells. A2, A3......
Wow! Thank you!
отлично, это то что я искал!
Thank you so much.
Thank you!
thank you!
🙏🏻
hello , i`m trying to combine the "indirect" with "arrayformula" and it`s not working , if u can give it a look i`d be thankful , here is a link to a small version i made for sharing.
docs.google.com/spreadsheets/d/1W7phC4MFG5cPhU5gxanY81tgsJAOOioU-EWP4h9nNxU/edit?usp=sharing
The problem is not indirect. The problem is the array formula fundamentally is flawed. Your VLOOKUP range will always stay the same range, it won't iterate though each option in the range.
the problem was that "indirect" can not work with "arrayformula" , so i summed up all my ranges in an array , and the "vlookup" searches for they key in all the ranges until if finds a match. problem solved , but i am not sure about speed difference if it searches in only one range or 12 ranges.
Glad you got it solved.Indirect has no issues with arrays it was just logically not working in VLOOKUP. If you search for VOOKUP(A1:A7,B1:B7,2,0) it will search VOOKUP(A1,B1:B7,2,0) , VOOKUP(A2,B1:B7,2,0) ,VOOKUP(A3,B1:B7,2,0) ,VOOKUP(A4,B1:B7,2,0) etc. It will not iterate through B1, B2, B3 separately.
Rename the Video Title - One Dropw... is quite simple... for multiple dropdown will never work!!!
work on your video it's fucking long you could finish it in a short time. watching long video is kinda boaring
THIS WAS NOT HELPFUL
HI!! I hope you can help me. Do you have an email address to send some questions?
Thank you!