Google Sheets - Dependent Drop Down Lists | 2 Ways
ฝัง
- เผยแพร่เมื่อ 22 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn how to create dependent dropdown data validation lists in Google Sheets and have different drop-down values based on the value of another cell. Basically the second drop down depends on the value of the first drop-down. Once we have the first drop down menu setup, we will create a dynamic dependent drop-down list that's based on the value of the first drop-down.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/sheets-depe...
There are different ways you can get this done depending on how your data is setup. If you have a tabular data set you might need another function than if your data is organized across multiple columns. I show 2 different ways of creating the dependent dropdown list depending on your setup.
00:00 Dependent Drop-Down Lists in Google Sheets
01:06 Dependent Drop-Down Lists in Google Sheets with Tabular Data
05:05 Dependent Drop-Down Lists in Google Sheets with Data in Report Format
LINKS to complete Google Sheets Playlist: • Google Sheets Tips, Tr...
_____________________________________________
★ My Online Excel & Google Sheets Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#googlesheets
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.
@@rmndvprz 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!
Leila, you have a gift for clear explanations, thoughtful edits and on -screen presence. Keep up the great work!
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 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.
That was incredible. This is exactly what I was looking for and it couldn’t have been explained any better. THANK YOU!
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
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!
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.
Leila, you have explained this so clearly. It has been really helpful. Really the best guide on TH-cam. Thanks 😊
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:
th-cam.com/video/ku17vgq4Q14/w-d-xo.html
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!
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!
How we apply this on entire column
same problem here :(
This video helped me SO much in a worksheet I'm creating. Thank you!
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.
Question about your dependent dropdown. This works great, however what I am doing, is instead of the second dependent dropdown, I have your FILTER formula which populates a second column which contains a link. For instance one is "Sales ID 70" and that is a link to a report. On the same sheet it works perfectly, providing the link to the report where the FILTER formula is.. however when I put it on the sheet I need and reference back to the sheet where it works, it provides the text "Sales ID 70" but no URL link. Any idea how to modify the FILTER on the other sheet to pull the URL with it, like it does on the sheet it's on? Thanks!
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.
THANK YOU LEILA...YOUR VID IS EXTREMELY HANDY!
Oh my gosh your intro is gold. Really helpful tutorial :)
Glad you think so!
How do we get rid of the "invalid" warning when we go back and select a new value from the first drop-down? This seems to be intentional with Google Sheets, i.e. not an error, but it quite distracting to user of a sheet with multiple drop down lists. Any idea how to get rid of it?
Hi Leila! thank you so much! Whats the link to the video where you explain how to expand the dependent list?
Does anyone know if, instead of having to click the drop down in the results column to display the matches and then click again to choose one, I can get all the options to dynamically display once the dropdown option in the first column is clicked?
you have made impressive and very helpful content on google sheets. Appreciate the videos
I love you. Sooo glad I found this channel at 4:47am. Thank you.
Welcome! You're up early 😴
Thx for keeping everything in one sheet in this tutorial😊
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?
Thank you Leila. Glad I found the video. My unique use case is that I would love for the list to go across cells and be unique to each cell. For Instance, lets say I want two dropdown lists in Column A (State) and Column B (County) and for each state I select from the dropdown in Column A, the list available in Column updates to contain only the counties of the state selected in Column A. And if I keep going down the state column to select a different state, the adjacent cell for Counties updates to contain only counties in the selected state in the adjacent cell.
I don't know if its possible, I have tried everything, even chatGPT
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?
Great, whether we can make dynamic dash board in Google sheet? Like power pivot in excel.
This is simply awesome. Saved a lot of my time. Thank you for the video.
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?
Big help. This video is simple and understandable for beginners like me. Thanks.
Exactly what I was looking for! Excellent!
Thank you. How can apply the first option(filter) to an entire column, to drag the formula with the data validation
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.
Very helpful, thanks. In my version of Google Spreadsheets, I had to replace the "," between RANGE and CONDITION by ";".... Thanks for that support video!
Very informative and easy to understand!
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 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?
Thank you , short , intensive and informative
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.
This is GOLD!! Helped me alot. Thanks.
Thanks for the helpful tutorial, can we add date also in this drop down i mean like manual date?
This is a great video. Thank you so much for sharing. How do you add options to the dependent list?
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?
Nice video like always , your channel is my go to for any google sheet magic trip
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?
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
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?
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
very helpful! it took me a while to figure it out but it worked!
Thanks for that. Perfect explanation and so useful. So used to Excel that I find it hard with the small bit in Google Sheets!
Do you know how to assign a value to a drop down item? For example, if I was trying to assign values to two items from two different drop down lists and add them together.
Very clear and concise explanation. I really appreciate you putting this together.
Glad you enjoyed it!
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.
th-cam.com/video/ku17vgq4Q14/w-d-xo.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!
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!
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
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...
You are terrific! I have just been learning google dox and find your videos invaluable. Thank you so much! I do have a question: What if you only have 1 cell in the 2nd drop down list relating to 2 or 3 cells in your original drop down list? There isn't a 'range' in this scenario and I can't seem to get the =filter working. Any suggestions?
thank you so much for this info!
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?
Thanks Leila. wonderful explation
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
You nicely explained what i needed thank u so much.
So helpful. This was exactly what I needed. 5 stars!!!!
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.
This tutorial just saved a lot of waste of energy trying to experiment...Thanks a lot you're a LIFE SAVER ! +1 sub
Thanks for the sub!
Hi, i created a dropdown list with list items in Google sheet. I want for last item naming "Other" enable any name or text want. You can say a sub category or sub dropdown type thing. Please help.
Thanks in Advance
Thank you very much Leila , that was very helpfull. :)
where is the next week video you mentioned about it at the end of this one ! May you provide its URL
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?
Help me please..how to copy multiple cells,and paste into table that i have use vba 🙏
A mind-blowingly simple explanation for something that has been a hurdle for me. THANK YOU.
You're very welcome!
Amazing, solved my problem. Super simple and clear to follow.
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!
great video. helped me with something i was stuck on
What about if you want more than 2 dependant drop downs?
For example: Games > Genre > Game Title
very nice and clear explanation . Great work🎉.... keep it up. Love from India❤
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
its there a way to grey out an option once selected? and ungreyed once deleted ?
Thanks Leila. I'm liking what I see in Google Sheets. I hope some of the innovations make it over to EXCEL. Thanks for sharing your tips :)) Thumbs up!!
I hope so too, Wayne!
IS THERE ANY WAY TO SELECT VALUE FROM DROP DOWN LIST AND ITS ADJACENT CELLS TAKE THE VALUE DEFINED IN THE MASTER SHEET?
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.
So, if I have 12 rows that have these dependent lists, Do I need 12 data prep columns?
Thanks for this! very helpful
Need help!!! Do i need to create data validation for each row? how do I get it on multiple rows?
Please Google sheets have been updated so can you help with the data validation for the new updated format
Thanks a lot, I tried for this.
Thank you so much for this!
this is fantastic, thank you!
thanks , this is what I want. do you provide class for google sheet and script?
thank you dear leila, it was so helpful
That was excellent. Thankyou.
Amazing thanks for your nice and neat tutorial. love it
hello,
need your help to create a dependent dropdown in *google form* using data or dependent dropdown menus from a google sheet. please guide me or if possible make a video on it.
P.S : Don't want to use logic based section shuffle or *form ranger* or *cascade formatting*.
thanks in advance
This is exactly what I was looking for. Thanks Leila
You’re welcome 😊
Thank You So Much ,
Wow, your explanation is priceless! Thank you so much :))
You're very welcome!
This makes google sheets sooio much more interactive.
This is awesome quality material. Thank you!
Glad you enjoyed it!
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