This will only work for one row. What if I want to add rows that will include the same function automatically without having to create new formulas, data prep columns, etc.?
Do you have any tips for creating this formula but when you need to repeat the same dependent data validation over and over? I'm trying to create a payroll sheet that basically calculates each specific job performed.
Exactly what I needed you are a saviour! UPD: I left the comment before watching the video, but as soon as you clarified that the data will be in the separate sheet, OMG just chefs kiss!
Hi Leila, I saw your video on Creating Dropdown lists in google sheets. In your video, you have shown the drop-down lists created in just two cells side by side, for example, A2 and B2. What if you want to create a series of drop-down lists across the entire column downwards. I'm trying to create an Operator Efficiency Monitoring Sheet, wherein, we have a Part Name and the drop-down dependent list I want to create is the List of Operations, that goes into making that particular Part. I have a multiple list of parts and on a daily basis, different parts are produced. So I want a matrix form of a sheet wherein every day, you select a different Part Name and its corresponding drop-down list opens. How will the Prep column behave in this case.
Leila explains this in the next video. Using transpose to make the list go horizontally across columns, then use this range as the source of the dropdown. It definitely works!
Great tool, what if I'm preparing a 600 line sheet? how can I do the multiple dependent lists for the 600 lines? will I create the (indirect) column 600 times? in excel: the (indirect) function can be written in the data validation range, but in Google Sheets it is not possible do you have an advise for that?
Hi Leila thanks for the video. The filter is conditioned on one single cell, on the first dropdown. I copied down the first dropdown to multiple rows, the filter does not change. It always reference the same cell.
Do have a video showing how make a word not show after you've entered from the drop down? E.g. I have a list of names in the drop down (Mark, Jenn, Steve, Mason, Gary). I want to enter Jenn, but after show Mark, Steve, Mason, Gary left in the drop down. I hope this makes sense. Thank you.
Hey Leila, thank you so much for the video, im havin trouble compleating the formula in teh data prep. row. After i start using the match function and try to match the dependant drop down list to the original list i get an error and it tells me "formula parse error". dont know what im doing wrong. got any tips?
Leila, your video is great! Need some help - Whenever I am copy pasting the rows the data validation does not catch the filter formulas. Can you suggest me a way out.
What if you want to have multiple sets of dependent dropdowns that reference the same data? For example, a list of transactions where each transaction has a dropdown for account type and account name? Do you have to have a separate data prep for each row in our transactions sheet?
I have managed to do this as an App Script- dependent on cell changes on previous Cell BUT I find it breaks when my original input is pulled from a formula- as it only changes when the data 'changes' and because it reads the formula and not the data itself it doesn't update unless you delete and re-enter the formula. - If you have found a better way let me know :)
This was extremely helpful. I would love to know how I can take this a step further and have the second dropdown automatically populate additional data into a 3rd cell?
This is great... however, what if i want the dependent data validation not in just one place (A4, B4) but i want the same validation for every row underneath those? - i guess i will be playing around with this for a bit...
I have been looking for something like that for a couple of days now, There is so many overly complicated ways out there but yours is simple and to the point
Thank you, this is helpful. I am having trouble making this work in consecutive dropdowns; for instance, it works in T2, the first dropdown, as expected, but then transfers that to all the dropdowns below in that column, no matter what I choose, because it is set to look for O2. So the dropdown in T3 still looks at O2. I tried extending the range, but it gave me an error. How can I fix this?
How can this process be carried down to multiple rows? Is there any way to copy this process from row two so that a mirrored process is possible in rows 3-50? Or would I need to have different columns capturing the "data prep" work for each row where I want to carry over this same conditional logic?
Great video! I need to ask if it is possible to make your own formula work in multiple rows. I am trying to find a way to use Dependent Data Validation in my Budget Check register. I have numerous Parent Categories and Sub-Categories. Please let me know if there is a way I can accomplish this. Thank you!
Hi Leila, this is so clear, thank you very much. Does this work with an "ImportRange" Function? I am trying to build templates which will be used with my data set held in another spreadsheet altogether.
This is great. The explanation up front about the tabular format vs report format was key and missing from all the other videos I was trying to use. Your stuff is always great, thanks!
Hi Leila, I want to know if the indirect function can be used in Google Sheet. I have tried both ways to create a check list with dependent data validation. However, I am not getting the desired output. When I am pasting the same data validation in the row below, I am not getting the exact data. Through Indirect Function in Excel, we can paste down the data validation to the below rows and have the same dependent drop down list. But that is not something I am seeing in Google Sheets.
Hello, the dropdown settings are not going to the next line. When i make the second dropdown for example in line 2 and i drag it down to line 10, the formular don`t work only in line 2
What if I select an option from the dropdown list and have it automatically populate other cells, but still be able to manually select on the cell? Is that workable?
Thank you. I did not want the second drop down, just one to fill the adjacent cell. I reworked your formula just a bit and it worked. Filter was what I needed not Vlookup what many others were showing, but didn’t work for me. Why it worked for them but not me, I don’t have a clue why. A lesson learned to watch your tutorials first. I still have to work out the #NA that is in the cell when nothing is chosen but will find a fix.
Thank you for video , But main issue as filter condition is =A4 is not dynamic. Because if I copy productivity / Games many times same does not work. Because we say that Filter condition . SO how =A4 change dynamically so its working fine? Pls help.
Thank You Mrs Leila for you great lesson, I have question, I need Like that list but not just for lists but one collumn or two in my table how I can doit it?
I had a question suppose in the current formula made. If suppose game division had only a single value and the others have multiple values and if i want the value for game division to auto populate is there a way to do the same ?
This is working for me on the first line, but when I apply the formula to multiple cells, the dependent drop down no longer works. Can anyone help me with this, please?
Simple and already well known by your teaching through excel videos, but still not less interesting the way video is presented. That really matters and is important. Well done.
How can you sort alphabetically your 1st column without messing up the formulas in the next column. When i sort by column A i get errors in all my column Bs
Thanks for the tutorial. Can you please help? I created the dropdown and protected the sheet except the dropdown cell, but every time someone changes the dropdown, it affects others view, can you please help?
Hi, First, thank you for this tutorial, it is excellent and helped me get way further in making this possible than other tutorials. However, I am still having trouble with making it happen on my reporting sheet. vs just on my masterdata sheet
I suck at spreadsheets. Every time I create one it is as if I'm using the program for the first time. After his video I accomplished at task in Sheets that I had never successfully applied before. Leila is brilliant, concise and beautiful. A combination that kept me focused on the task at hand resulting in exactly what I had been attempting for far too long
haha not sure the "beautiful" remark was necessary, but you got a heart! It's a great video! Just want to know whether i can do multiple rows with the same data validation or not
once data prep is done and applied to first cell, how do you apply it to entire column for dependent drop down. Copy paste just relies on same first cell
Leila, Thanks. This helped. I am trying to Create of users that Own a particular Boat. They select that boat from the list and it references that Data page. My issue is that i feel like i need a separate Data page for each user or the data Prep field that uses the filter is only pointing to the first user. Do you have any thoughts?
hello, I don't know where the problem is, but even if I do everything according to the instructions, it doesn't work for me and it keeps giving me an error in the cell with the Data Prep formula. I downloaded the sample document and it works by itself, but as soon as I copy it directly into my own sheet without editing, the whole thing stops working. I don't know what to do.
ok, i found the problem now. I don't know why it is shown everywhere (including the official descriptions of functions from Google) that commas should be used when there should be semicolons. It already works as it should with them.
Thank you for this. Question - So my list have instructions in the cell next to it, they are located in a separate sheet tab. I want to make a drop down on a different sheet which I know how to do it but how do yo carry the instructions for that list? Is there a way? Thanks!
@@egymade I don't think I'm following your question. This video showed how to insert a dropdown into a single cell that referenced a column of data. What are you trying to do in the entire column?
@@matthewmorrisson1806 thanks for reply, appreciated! I wanna create 2 full columns not just 1 cell. The 1st column is a drop down menu which is easy to do.. The 2nd is the dependent menu on the 1st column.. So whenever I choose any cell of the 1st column the corresponding cell changes in the 2nd.
Same question. When I use the same steps for the dependant drop-down menu in all other rows except the first it only shows the drop-down options for the item I chose in the first row. So now my dependent drop-down list only shows the options for one item in the first drop-down list. Because of this formula =index(A4:J12,,match(Budget!C5,A3:J3,0)) The C5 is the issue because it's reading the item I chose on the drop-down list from my first entry. How do I change the formula to know to look at the cell of the current row I'm working in? ((I am sure the way I am wording all of this is hard to understand...my apologies!))
Hi liela I have created Drop-Down List of project number which are in column A from row 6. But now I want Drop-Down List which will show only invoice number parallel to selected project number from column B. I could not able to create that Drop-Down List so can you help me to solve it?
Grab the file I used in the video from here 👉 pages.xelplus.com/sheets-dependent-dropdown
When i click data validation, there is a pop up appear from side screen, not a dialog box like yours, why is that?
@@fadly1896 This video was 3 years ago. Google has made several visual updates, and that included the side-bar/screen instead of a pop-up.
@@deeeyv i see, thank you
Is there any way you can do that repeatedly? E.g. the data validation options would be from A1:A10 (category) and B1:B10 (subcategory)?
@@meganesguerra7540 came to know the same thing. it only works for 1 cell!
This will only work for one row. What if I want to add rows that will include the same function automatically without having to create new formulas, data prep columns, etc.?
Same issue with me
It seems she left that part for a second video,
ua-cam.com/video/ku17vgq4Q14/v-deo.html
Exactly, i encountered this issue too
Do you have any tips for creating this formula but when you need to repeat the same dependent data validation over and over? I'm trying to create a payroll sheet that basically calculates each specific job performed.
I believe this video will solve your problem:
ua-cam.com/video/ku17vgq4Q14/v-deo.html
Leila, you have a gift for clear explanations, thoughtful edits and on -screen presence. Keep up the great work!
Well done. One of the best, if NOT the best, youtube demonstration I've seen in many years.
Simply Explained the Functionality and very easy to Understand. Very Nice!!! :)
Thank you so much for this! I'm currently making my expenses tracker for 2023, and this is EXACTLY what I was looking for to categorize my expenses.
Exactly what I needed you are a saviour!
UPD: I left the comment before watching the video, but as soon as you clarified that the data will be in the separate sheet, OMG just chefs kiss!
Glad it was helpful.
Hi Leila, I saw your video on Creating Dropdown lists in google sheets. In your video, you have shown the drop-down lists created in just two cells side by side, for example, A2 and B2. What if you want to create a series of drop-down lists across the entire column downwards. I'm trying to create an Operator Efficiency Monitoring Sheet, wherein, we have a Part Name and the drop-down dependent list I want to create is the List of Operations, that goes into making that particular Part. I have a multiple list of parts and on a daily basis, different parts are produced. So I want a matrix form of a sheet wherein every day, you select a different Part Name and its corresponding drop-down list opens. How will the Prep column behave in this case.
ua-cam.com/video/ku17vgq4Q14/v-deo.html&ab_channel=LeilaGharani
exactly my question too
Leila explains this in the next video. Using transpose to make the list go horizontally across columns, then use this range as the source of the dropdown. It definitely works!
How we apply this on entire column
same problem here :(
@@kayasami898
It seems she left that part for a second video,
ua-cam.com/video/ku17vgq4Q14/v-deo.html
It seems she left that part for a second video,
ua-cam.com/video/ku17vgq4Q14/v-deo.html
Thank you. How can apply the first option(filter) to an entire column, to drag the formula with the data validation
Great tool, what if I'm preparing a 600 line sheet? how can I do the multiple dependent lists for the 600 lines? will I create the (indirect) column 600 times?
in excel: the (indirect) function can be written in the data validation range, but in Google Sheets it is not possible
do you have an advise for that?
She has a video about that! ua-cam.com/video/ku17vgq4Q14/v-deo.html
Hi Leila thanks for the video. The filter is conditioned on one single cell, on the first dropdown. I copied down the first dropdown to multiple rows, the filter does not change. It always reference the same cell.
I have the same issue. How do I apply this to multiple cells?
@@MissJCAnn Would love to know this as well
Remove the fix $ sign on the first row, then when paste, choose validation only
@@laissezfairez Can you expand on this?
My problem is the same.
WOW, I was breaking my head trying to use IF statement to do this and it is so simple.
Thank you, really helped me now.
4:56 but what if I need multiple chips, once you made a selection in Col A the options in Col H stay, so I can only use the chip in Col A once?
Do have a video showing how make a word not show after you've entered from the drop down? E.g. I have a list of names in the drop down (Mark, Jenn, Steve, Mason, Gary). I want to enter Jenn, but after show Mark, Steve, Mason, Gary left in the drop down. I hope this makes sense. Thank you.
Hey Leila, thank you so much for the video, im havin trouble compleating the formula in teh data prep. row. After i start using the match function and try to match the dependant drop down list to the original list i get an error and it tells me "formula parse error". dont know what im doing wrong. got any tips?
Leila, your video is great! Need some help - Whenever I am copy pasting the rows the data validation does not catch the filter formulas. Can you suggest me a way out.
What if you want to have multiple sets of dependent dropdowns that reference the same data? For example, a list of transactions where each transaction has a dropdown for account type and account name? Do you have to have a separate data prep for each row in our transactions sheet?
Did you ever figure this out?
ua-cam.com/video/ku17vgq4Q14/v-deo.html&ab_channel=LeilaGharani
I have managed to do this as an App Script- dependent on cell changes on previous Cell BUT I find it breaks when my original input is pulled from a formula- as it only changes when the data 'changes' and because it reads the formula and not the data itself it doesn't update unless you delete and re-enter the formula. - If you have found a better way let me know :)
I have the same problem have you solve this one by now?
This was extremely helpful. I would love to know how I can take this a step further and have the second dropdown automatically populate additional data into a 3rd cell?
This is great... however, what if i want the dependent data validation not in just one place (A4, B4) but i want the same validation for every row underneath those? - i guess i will be playing around with this for a bit...
I love you. Sooo glad I found this channel at 4:47am. Thank you.
Welcome! You're up early 😴
Oh my gosh your intro is gold. Really helpful tutorial :)
Glad you think so!
I have been looking for something like that for a couple of days now, There is so many overly complicated ways out there but yours is simple and to the point
Thank you, this is helpful. I am having trouble making this work in consecutive dropdowns; for instance, it works in T2, the first dropdown, as expected, but then transfers that to all the dropdowns below in that column, no matter what I choose, because it is set to look for O2. So the dropdown in T3 still looks at O2. I tried extending the range, but it gave me an error. How can I fix this?
That was incredible. This is exactly what I was looking for and it couldn’t have been explained any better. THANK YOU!
How can this process be carried down to multiple rows? Is there any way to copy this process from row two so that a mirrored process is possible in rows 3-50? Or would I need to have different columns capturing the "data prep" work for each row where I want to carry over this same conditional logic?
Thanks for the helpful tutorial, can we add date also in this drop down i mean like manual date?
The formula works and I am thrilled how you simplified the tutorial! I wonder, how can I replicate the single cell example into multiple dropdowns?
What if I need this for every row? I can't put a column for every row. What should I do?
Great video! I need to ask if it is possible to make your own formula work in multiple rows. I am trying to find a way to use Dependent Data Validation in my Budget Check register. I have numerous Parent Categories and Sub-Categories. Please let me know if there is a way I can accomplish this. Thank you!
Very clear and concise explanation. I really appreciate you putting this together.
Glad you enjoyed it!
Hi Leila, this is so clear, thank you very much. Does this work with an "ImportRange" Function? I am trying to build templates which will be used with my data set held in another spreadsheet altogether.
Leila, you have explained this so clearly. It has been really helpful. Really the best guide on UA-cam. Thanks 😊
Please Google sheets have been updated so can you help with the data validation for the new updated format
This is great. The explanation up front about the tabular format vs report format was key and missing from all the other videos I was trying to use. Your stuff is always great, thanks!
Thank you so much.. I was trying to do this using name list bcz of some certen video.. I was trying more than 3h.. You just save me
Hi Leila, I want to know if the indirect function can be used in Google Sheet. I have tried both ways to create a check list with dependent data validation. However, I am not getting the desired output. When I am pasting the same data validation in the row below, I am not getting the exact data. Through Indirect Function in Excel, we can paste down the data validation to the below rows and have the same dependent drop down list. But that is not something I am seeing in Google Sheets.
Please help with that.
Hello, the dropdown settings are not going to the next line. When i make the second dropdown for example in line 2 and i drag it down to line 10, the formular don`t work only in line 2
What if I select an option from the dropdown list and have it automatically populate other cells, but still be able to manually select on the cell? Is that workable?
Very helpful but my problem remains the same as drop-down created by these cannot be used in multiple rows down
So, if I have 12 rows that have these dependent lists, Do I need 12 data prep columns?
Thx for keeping everything in one sheet in this tutorial😊
where is the next week video you mentioned about it at the end of this one ! May you provide its URL
IS THERE ANY WAY TO SELECT VALUE FROM DROP DOWN LIST AND ITS ADJACENT CELLS TAKE THE VALUE DEFINED IN THE MASTER SHEET?
I am receiving an error message: Did not find value '' in MATCH evaluation. Data prep formula is: =index(A2:J15,,match('PP1'!B5,A1:J1,0))
Hi Leila! thank you so much! Whats the link to the video where you explain how to expand the dependent list?
Need help!!! Do i need to create data validation for each row? how do I get it on multiple rows?
Thank you. I did not want the second drop down, just one to fill the adjacent cell. I reworked your formula just a bit and it worked. Filter was what I needed not Vlookup what many others were showing, but didn’t work for me. Why it worked for them but not me, I don’t have a clue why. A lesson learned to watch your tutorials first. I still have to work out the #NA that is in the cell when nothing is chosen but will find a fix.
very nice and clear explanation . Great work🎉.... keep it up. Love from India❤
Thank you for video , But main issue as filter condition is =A4 is not dynamic. Because if I copy productivity / Games many times same does not work. Because we say that Filter condition . SO how =A4 change dynamically so its working fine? Pls help.
What about if you want more than 2 dependant drop downs?
For example: Games > Genre > Game Title
What if we have to do the same in the report sheet instead? How can we use this?
Thanks Leila. wonderful explation
Thank You Mrs Leila for you great lesson, I have question, I need Like that list but not just for lists but one collumn or two in my table how I can doit it?
what if I need to add more drop-down in Column A? The filter function is not picking up the correct details on column B.
I had a question suppose in the current formula made. If suppose game division had only a single value and the others have multiple values and if i want the value for game division to auto populate is there a way to do the same ?
This is awesome quality material. Thank you!
Glad you enjoyed it!
So helpful. This was exactly what I needed. 5 stars!!!!
This video helped me SO much in a worksheet I'm creating. Thank you!
great video. helped me with something i was stuck on
Thanks. Just what I needed.
This is working for me on the first line, but when I apply the formula to multiple cells, the dependent drop down no longer works. Can anyone help me with this, please?
Simple and already well known by your teaching through excel videos, but still not less interesting the way video is presented. That really matters and is important.
Well done.
So nice of you!
@@LeilaGharani do you know how to do it for the entire column? the video made it for 1 cell only and doesn't work for the rest of the column!
How can you sort alphabetically your 1st column without messing up the formulas in the next column. When i sort by column A i get errors in all my column Bs
very helpful! it took me a while to figure it out but it worked!
Thanks for the tutorial. Can you please help? I created the dropdown and protected the sheet except the dropdown cell, but every time someone changes the dropdown, it affects others view, can you please help?
Thanks a lot mam 🙂🙂
Hi,
First, thank you for this tutorial, it is excellent and helped me get way further in making this possible than other tutorials.
However, I am still having trouble with making it happen on my reporting sheet. vs just on my masterdata sheet
Thank you very much Leila , that was very helpfull. :)
Big help. This video is simple and understandable for beginners like me. Thanks.
thank you dear leila, it was so helpful
I suck at spreadsheets. Every time I create one it is as if I'm using the program for the first time. After his video I accomplished at task in Sheets that I had never successfully applied before. Leila is brilliant, concise and beautiful. A combination that kept me focused on the task at hand resulting in exactly what I had been attempting for far too long
haha not sure the "beautiful" remark was necessary, but you got a heart!
It's a great video! Just want to know whether i can do multiple rows with the same data validation or not
once data prep is done and applied to first cell, how do you apply it to entire column for dependent drop down. Copy paste just relies on same first cell
its there a way to grey out an option once selected? and ungreyed once deleted ?
Finally found a good video for this
You nicely explained what i needed thank u so much.
thank you!
THANK YOU LEILA...YOUR VID IS EXTREMELY HANDY!
Great video. thankyou so much.
Is the tutorial to apply this to the entire column out yet?
ua-cam.com/video/ku17vgq4Q14/v-deo.html
when i keep the drop down cell in other sheet and data prep formula on other the filter or index doesnt work. how we can fix this
Leila, Thanks. This helped. I am trying to Create of users that Own a particular Boat. They select that boat from the list and it references that Data page. My issue is that i feel like i need a separate Data page for each user or the data Prep field that uses the filter is only pointing to the first user. Do you have any thoughts?
excellent, you have earned a subscribition and a comment
How do I do that using data from a different sheet?
hello, I don't know where the problem is, but even if I do everything according to the instructions, it doesn't work for me and it keeps giving me an error in the cell with the Data Prep formula. I downloaded the sample document and it works by itself, but as soon as I copy it directly into my own sheet without editing, the whole thing stops working. I don't know what to do.
ok, i found the problem now. I don't know why it is shown everywhere (including the official descriptions of functions from Google) that commas should be used when there should be semicolons. It already works as it should with them.
I just loving you now. really what I needed
Very nicely explained
It is always pleasant to watch Your videos.....Thanks -- From Pakistan
I'm glad to hear that :)
Hopefully next time the data use are something you can easily connect to.
Thank you for this. Question - So my list have instructions in the cell next to it, they are located in a separate sheet tab. I want to make a drop down on a different sheet which I know how to do it but how do yo carry the instructions for that list? Is there a way? Thanks!
That's exactly what I needed and w/o a bunch of complex lookup tables. Thanks!!
do you know how to do it for the entire column? the video made it for 1 cell only and doesn't work for the rest of the column!
@@egymade I don't think I'm following your question. This video showed how to insert a dropdown into a single cell that referenced a column of data. What are you trying to do in the entire column?
@@matthewmorrisson1806 thanks for reply, appreciated!
I wanna create 2 full columns not just 1 cell. The 1st column is a drop down menu which is easy to do.. The 2nd is the dependent menu on the 1st column.. So whenever I choose any cell of the 1st column the corresponding cell changes in the 2nd.
This makes google sheets sooio much more interactive.
Thank you for the video. Is there a way to have multiple rows filtering, like thousands of rows?
Same question. When I use the same steps for the dependant drop-down menu in all other rows except the first it only shows the drop-down options for the item I chose in the first row. So now my dependent drop-down list only shows the options for one item in the first drop-down list. Because of this formula =index(A4:J12,,match(Budget!C5,A3:J3,0)) The C5 is the issue because it's reading the item I chose on the drop-down list from my first entry. How do I change the formula to know to look at the cell of the current row I'm working in? ((I am sure the way I am wording all of this is hard to understand...my apologies!))
@@brandonnolan6222 I think I have the same question... the only solution I can think of is having a data prep for each row.
@@brandonnolan6222 ua-cam.com/video/ku17vgq4Q14/v-deo.html
Haven't watched yet but I'm assuming this is the solution.
@@BryanAlejandro_ YESSS MAN! This was it! Thanks, man!
Linked drop-down menus are not available on mobile. It gives the error "Loading data validation options, please try again"
you have made impressive and very helpful content on google sheets. Appreciate the videos
That first method doesn’t work if you want multiple drop down lists because the FILTER function will only be conditioned to that one cell.
Same issue for me. Did you ever find a solution to this problem?
This is a great video. Thank you so much for sharing. How do you add options to the dependent list?
thank you so much for this info!
Hi liela
I have created Drop-Down List of project number which are in column A from row 6. But now I want Drop-Down List which will show only invoice number parallel to selected project number from column B. I could not able to create that Drop-Down List so can you help me to solve it?
thanks , this is what I want. do you provide class for google sheet and script?