Dude. You saved my bacon. Just organised my school's swimming carnival. Easiest video to follow along with by far. Thanks for all the hard work you've put in.
@beeldbuijs1003
Жыл бұрын
Interesting technique (the split etc). However, when you need multiple levels of dependent dropdown menus, you often don't have unique far right items. If you for example have 10 types of clothes x 10 models x 5 sizes x 5 colors = 2500 items, you still get a dropdown menu that is too long, of at least 250 items, when typing an attribute.
@HashAliNZ
Жыл бұрын
Yeah ya gotta decide which method and tool is better for your particular data
@hollydurfee3214
6 ай бұрын
I have watched so many videos and yours is the only one that made sense to me. Thank you so much!!!
@SaraLaidlawASBINC
Жыл бұрын
I had to give up Excel for Google Sheets for one simple feature. Searchable dropdowns. Excel is still clicking/scroll. Unreal.
@HashAliNZ
Жыл бұрын
There is a way to have searchable dropdowns in Excel, but oh man it's complicated!
@GüzinKöklüSezer
8 ай бұрын
how can we do this if our data is in report format not tabular format?
@gobindakhanal6973
Ай бұрын
This is what I needed for a long time
@MrAarong4
Жыл бұрын
This has been a much easier to follow guide for creating dependant drop downs. Dont suppose you have any insight into how I could do the following. I am essentially creating a quoting spreadsheet based off an existing catalogue which is in my sheets - so I am looking for sheets to fetch the price that relates to all three drop-downs being correct ie ( Code > Size > Colour ) the prices exist in the catalogue I just need it to pull the single price in once the drop downs are filled TIA if you have an idea of this - if not also not a problem
@HashAliNZ
Жыл бұрын
Yeah, absolutely. We can do that with a vlookup. Super easy in Google Sheets. Much harder in Excel. Here's an example spreadsheet, with the dropdowns in column H and the formula in column I: docs.google.com/spreadsheets/d/18PurRyWonR4l5vVn37BndS7lydCbTpGkfjn_hULx07E/edit?usp=sharing
@MrAarong4
Жыл бұрын
@@HashAliNZ That's awesome thank you :) I will have a play about with that and see what I can do
@bongassociatesanalytics124
Жыл бұрын
This is the best video for Dependent Drop downs in google sheets. Kudos!
@HashAliNZ
Жыл бұрын
Thanks so much for the kind words!
@stuttgart9000
Жыл бұрын
Superb video, so glad I found Hash, smart guy :)
@vivienneandrews5684
Жыл бұрын
Amazing, I love your instructions, very clear.
@lauramontoya2222
Жыл бұрын
super helpful, thanks! I'm curious if I can do this by grabbing data from another spread sheet
@ufktrhn
Жыл бұрын
you can do it with import range
@afiesty12
8 ай бұрын
This is super helpful. Thank you for sharing this.
@psiklone1
Жыл бұрын
I took on something much more complicated than I initially thought as an amateur, and you made it work amazingly for me. I will be constantly adding to the sheet I was working on to make it better, but this was an amazing start. Thank you so very much.
@HashAliNZ
Жыл бұрын
That's fantastic to hear, Edward! Keep on learning and improving your skills!
@KayHurricane
7 ай бұрын
Thank you for this amazing explanation and video! :)
@gilson7959
9 ай бұрын
This help me a lot! Thank you!!
@JennTurner-4asl
2 ай бұрын
Is there a way to sort by drop down status? I want "follow up" drop down status to be priority in my list.
@pameladuran2778
Жыл бұрын
Thank you so much for this. I had spent hours trying yo get it done in Excel😅. Do you know if there is a way to clear the values whenever you select a different item from the drop down list? Instead of having to do it manually. I would appreciate any feedback. Thank you again!❤
@HashAliNZ
Жыл бұрын
Hi Pamela, The only way to do it without just hitting the delete key is to use a script. Here's a quick example just a single row of dropdowns: function onEdit(e) { const sheet = e.source.getActiveSheet(); const range = e.range; if (range.getA1Notation() === "A2") { sheet.getRange("B2").clearContent(); sheet.getRange("C2").clearContent(); } // Check if the edit was made to cell Q7 if (range.getA1Notation() === "B2") { sheet.getRange("C2").clearContent(); } }
@fromuday
10 ай бұрын
Hi Hash, this video is very helpful. I have a question... My columns are dynamic, meaning that they could increase in the future. I would like to have all the column headers populated into a dropdown. Selecting a value from the dropdown, I want the rows in that column to be displayed in the adjacent cell. In simpler terms, let's say we have a sheet called Positions, that has columns: Position1, Position2, Position3,... They have rows such as Skill1, Skill2, Skill3, ... There is no limit to the Positions or the Skills, meaning, the table can increase bidirectionally. A1 has a dropdown to pick a Position. All the skills (rows) underneath this Position should be populated in B1:B. A3 has a 2nd dropdown to pick the 2nd Position. All the skills (rows) underneath the 2nd Position should be populated in B3:B. A8 has a 3rd dropdown to pick the 3rd Position, and so on... Dropdowns in column "A" can occur sporadic. Have you come across this situation and know the solution to it? Any help on this would be much appreciated.
@HashAliNZ
10 ай бұрын
That sound complex! Can you share a copy of your spreadsheet and a mockup of your desired result? Share at themathlabnz@gmail.com
@slybesker
8 ай бұрын
Sadly, when accessing google tables from mobile phone, its not searching the row, only if you write starting with first letters of entire row
@elvietrasonapetrache-padua2989
Жыл бұрын
Thank you for sharing this. I need this so much at work.
@HashAliNZ
Жыл бұрын
You're very welcome!
@peterpan-hg8fs
2 ай бұрын
ohhh.... woooowwww thanks man.. problem solved!!! i will recommend you..
@mottep.
5 ай бұрын
Love it. My problem is solved. Thanks.👍👍
@HashAliNZ
5 ай бұрын
Fantastic! There's also another method shown here if you want something different: kzitem.info/news/bejne/t4eK2KuZZpd-iaAsi=cG60UpXna8gYuoBF
@lijojoseph8572
10 ай бұрын
This is what I'm looking for, but there's something I need to add that sheet link to the Google form. Please advise.
@HashAliNZ
10 ай бұрын
Hey! To add a Google Form, you can either link directly to the spreadsheet you're working on, or you can create a new spreadsheet and use importrange. It all depends on your current structures
@lijojoseph8572
10 ай бұрын
@@HashAliNZ You created a '@dropdown' cell linking country to city right. So Can the same dropdown be shown for item lists in a Google form? I want to select a country and show relevant lists of cities in the Google form. Please help
@HashAliNZ
10 ай бұрын
Hi @@lijojoseph8572, unfortunately no. The only way to do that in Google Forms is to use conditional logic. So when you create a dropdown list of countries, you'll then add a new section for each country and then in that new section list out all the relevant cities. It's a huge pain. A better option, depending on how you're using the form would be creating something with AppSheet using the _isValid feature
@GeorgeKoenig-bh6eh
3 ай бұрын
Awesome, thank you!
@deonlubbe6127
8 ай бұрын
Thank you very much Hash. Do you have a list of short cuts for special characters like the arrow you used with Alt 26
@HashAliNZ
8 ай бұрын
I've never had one before, but I just whipped this one up now for you. Thanks! docs.google.com/spreadsheets/d/14a5ckeHhqTkBoi_MBt8u5WYCNRmxsDcOsDOXJjRvw-s/edit?usp=sharing
@yasufumithebest
Жыл бұрын
Nice content, subscribed. I have some questions not sure you could help, is out of topic. I want to sum to value below cell but I want it to stop when it detected the empty cell and done sum up the value below the empty cell.
@ayoubessaki5293
4 ай бұрын
i needed just the first dropdown list but i have to make a dropdown query for each vertical dropdown list, Understand ?
@HashAliNZ
4 ай бұрын
Sorry, I'm not quite sure what you mean. Can you expand a bit?
@martinjudd952
Жыл бұрын
I loved it!!! It is exactly what I’ve been looking for in order to search on 7,000 items with 3 columns of dependents … And then I noticed it was NOT in excel. 🤦♂️ Is this doable in Excel or am I just S.O.L?🤢 What would you suggest? 🙏🙏🙏 Learn Sheets? 🤷♂️
@HashAliNZ
Жыл бұрын
While it's doable in Excel, you can't do the autocomplete for partial matches like you can in Google Sheets. Not yet, at least. There are other ways to do multiple dropdowns. Check out Leila's video here: kzitem.info/news/bejne/mKOlmneFp5lohpw
@martinjudd952
Жыл бұрын
@@HashAliNZ Leila does have a solution for an autocomplete solution but it’s only available on some versions of 365. Unfortunately my latest update is not one of them. She has a more recent solution using the filter function where the solution is transposed into one row. This allows new pull downs in successive rows. It works. And she has been so helpful, but to use multiple columns it becomes cumbersome. Your solution was so elegant and so refreshing you can imagine my disappointment to see it was in Sheets. Oh sheet!!! So many improvements over Excel I may have to learn to do some new Sheet!😎 thank you so much for the quick response. 🙏🙏🙏
@HashAliNZ
Жыл бұрын
@@martinjudd952 Well if you do make the shift, you know where to find me for help!
@martinjudd952
Жыл бұрын
@@HashAliNZ ❤️ Yes I do!!
@martinjudd952
Жыл бұрын
My son loves Kiwi’s, always so helpful 😉
@BiswadeepMukherjee-w9i
Жыл бұрын
Thanks..It helped me a lot
@HashAliNZ
10 ай бұрын
Glad it helped
@DPortal17
Жыл бұрын
Super helpful! thnx a million bro
@HashAliNZ
Жыл бұрын
Always happy to help, Rami!
@petermarsh2174
Жыл бұрын
I am trying to add if formulas in another coloumn based on city but am unable to capture the trigger city any ideas please
@HashAliNZ
Жыл бұрын
Hi Peter, Can you share what you've got so far?
@petermarsh2174
Жыл бұрын
@@HashAliNZ docs.google.com/spreadsheets/d/1olLzoriP-l7Of8d_WEX-dQWlrT1V_QhLyUexL1bXzYE/edit?usp=sharing USD → only plans wanting to get E3 working Thanks
@petermarsh2174
Жыл бұрын
Hi Mate did you have any luck with what could be going on in this spreadsheet for it not to work. @@HashAliNZ
@HashAliNZ
Жыл бұрын
@@petermarsh2174Can you share your spreadsheet? Feel free to email it to me at themathlabnz@gmail.com and I can take a look
@gadd99
Жыл бұрын
Nice video. Is it possible to make it where you select a country FIRST and then continent automatically fills up? Kind of reverse but while still maintaining the same structure or order of dropdowns.
@HashAliNZ
Жыл бұрын
Yeah, you can put it in any order you want - just change it up however works for you. Usually we do continent first, then country because a country lies within a continent. Start big, go small.
@gadd99
Жыл бұрын
@@HashAliNZ Thanks for the reply, but I had something different in mind. Is there a way to make it so where you can have both happen at the same time - you can click on the left most column to find a match on the right; OR click on right column to find a match for the left column? This is something I am interested in this because I have products and descriptions and I am looking for a way that someone can dropdown a product and description matches and vice versa - can click on description dropdown to match a product.. (basically while just using two dropdowns) Not sure if I make sense here..
@HashAliNZ
Жыл бұрын
@@gadd99 do you mean like a product name in one cell and a sku in the other?
@NeerajAgarwal
Жыл бұрын
This is amazing. Thank you for sharing. :)
@stanleypaul1381
Жыл бұрын
wowza!!!...never thought I would find the Pabby wizard here in the commet section! And didn't know you were also on a look out for a solution to this problem. I had the same requirement and solved it with Apps Script. Check comments for the google forum link.
@panchaljignesh88999
Жыл бұрын
thanks This made my work so much easy...
@HashAliNZ
Жыл бұрын
Perfect!
@johnmtconnolly
Жыл бұрын
Super helpful!
@HashAliNZ
10 ай бұрын
Glad it was helpful!
@WilliamAnglissInstituteatSLIIT
Жыл бұрын
Very useful, Thank you.
@HashAliNZ
Жыл бұрын
You are welcome!
@CarolynQui
Жыл бұрын
Help! Somebody! It is not working. Is it because I asked for the person name in the second column after the time stamp. I need those two. Should I move them and put the lists in the first three columns?
@HashAliNZ
Жыл бұрын
Hi Carolyn, Is this related to this video? There's no timestamps column in this
@CarolynQui
Жыл бұрын
@@HashAliNZ no there is not time stamp but each google form that I create comes with a time stamp and the column can't be deleted. I'm new at this process. I will have to come up with another way as the form is needed for today. I'm not sure how to proceed.
@marcat3849
3 ай бұрын
Is there a way of hiding the two other methods in a different sheet?
@CarolynQui
Жыл бұрын
This is good - I have 20 High Schools, 20 Middle Schools, 26 Elementary Schools Does the formatting of the columns change?
@HashAliNZ
Жыл бұрын
Hi Carolyn, Your first column should be the 'type', in this case the type of school (e.g., 20 rows of "High School", 20 rows of "Middle School", and 26 rows of "Elementary School". Your second column should be the name of the school.
@bshafeek1
Жыл бұрын
Is the second solution for multiple rows a dependant drop down , I dont think so...its only a workaround
@HashAliNZ
Жыл бұрын
A workaround that totally works! That sounds like success to me!
@EmmanuelEvangelista-r4b
Жыл бұрын
Is it possible to use a combination of multiple dependent dropdown lists and query function?
@HashAliNZ
Жыл бұрын
Hmmm I've never tried, but I don't see why not!
@George1966ism
Жыл бұрын
Thanks&thanks&thanks! I have been trying for weeks to make sense of a huge sheet of lists. Now I know many things about Google Sheets and finally the perfect solution to my problem. Can't wait to impress the guys at the office. - Yey/cutter/in/an/burg (like Ham-burger) :)
@TylerAlbers01
11 ай бұрын
I find it amusing how "The Other (Better) Method" is just a normal, single picklist field instead of the "ENDLESS Dependent Drop-Down List... For Every Row" that the title of your video claimed. I was drawn in by the easy to follow explanations good pacing, but the bait-and-switch is super uncool. Is there actually a way to have, for instance, 3 total dropdown lists for every row, where 2 of those dropdowns are dependent picklists? And if it's not possible, why try to trick people into thinking it is possible?
@HashAliNZ
11 ай бұрын
Hey Tyler! You're right, it's not actually "endless" dependent dropdown list for every row. We can only get 18,726 dependencies and 50,000 characters in one cell, or about 400,000 rows with dropdown lists (depending on how many dependencies you've got). However, if you've got more than just 3 dependencies and you're clicking the first which filters the second which filters the third, which filters the final one, that's four searches and clicks and a huge time dump! And if you've got 400,000 rows, you're using the wrong tool. This "Other (Better) Method" is literally type what you want and it will pop up - only one dropdown list to save time instead of having multiple. No multi-clicking, no timewasting, no bait-and-switch tricks here - it's exactly as advertised (except for the 'endless' part) - It's virtually endless for pretty much every use case, it's got dependencies, it's got dropdown lists for every row. This method is absolutely better for when there is more than two dependencies. As for your question, yes if you want multiple dependent dropdown lists, then that is totally doable. You'll need a script to do it, or use a better program designed for drilling down - like Looker Studio. Here's a Google Sheet that has multiple dropdowns lists, multiple rows using a script to populate them. There is a slight time delay between selecting the dropdown item and the next one populating since it's script-based instead of formula-based: docs.google.com/spreadsheets/d/1qhIZrQazNN96PYrI_5dU2WPgikMWxTcM3g7KuIMxOmk/edit?usp=sharing
@stuttgart9000
Жыл бұрын
Great video, you are Super Smart :)
@HashAliNZ
Жыл бұрын
Thank you so much, Cecile! I appreciate the super smart comment. I just see myself as experienced
@chakra531
Жыл бұрын
Awesome!
@HashAliNZ
Жыл бұрын
Thanks daryl!
@MysticMango09
Жыл бұрын
if the query function doesnt work for you, try to use ; instead of ,
@HashAliNZ
Жыл бұрын
Absolutely! Semicolon instead of comma for our European friends, and backslash instead of semicolon if you want to list things down instead of across!
@HashAliNZ
Жыл бұрын
Ok, ok! Let's be honest, it's not *REALLY* an INFINITE number of dependencies , only up to 50,000 characters in one cell. But hey, when are you ever gonna use that much?! Woops! That's not my face at 00:15! Oh well.. better luck next time!
@stanleypaul1381
Жыл бұрын
If you want more than 50,000 characters in a single cell, you can use QUERY's header clause as '9^9' --- from the internet :)
@lebi87
Жыл бұрын
Amazing!
@HashAliNZ
Жыл бұрын
Thank you! Cheers!
@Testing-eb8hi
5 ай бұрын
second option is good, first one dont work smartly
@HashAliNZ
5 ай бұрын
Totally agree, but we can't forget about this extra method: kzitem.info/news/bejne/t4eK2KuZZpd-iaAsi=cG60UpXna8gYuoBF
@AnneNon
4 ай бұрын
I wouldn't really call this method "dependent" drop-down lists...
@HashAliNZ
4 ай бұрын
Call it what you want - one column is dependent on another, it's all just in one dropdown instead of multiple and it's all searchable. If you want traditional dependent dropdown lists, I've got that covered here: kzitem.info/news/bejne/t4eK2KuZZpd-iaAsi=s5xxieJVq81ZCcJV
@antondementiev2188
Жыл бұрын
Bonus points for pronouncing Ekaterinburg correctly! 😂
@HashAliNZ
Жыл бұрын
Really?!? I got it! Wow I was not expecting to get it right 🤣🤣
@haleybraner2481
Жыл бұрын
i love you
@haleybraner2481
Жыл бұрын
its so incredible that you took the time to make this tool - youtube university stays winning!!!!
@HashAliNZ
Жыл бұрын
@@haleybraner2481 Always happy to help, Haley. If you have anything specific, just let me know!
@johncipolla8335
Жыл бұрын
hello. I want to extract data from a master list and have a sort criteria or filter for say one persons name from that list
@HashAliNZ
Жыл бұрын
Hi John, In your new sheet pick a cell to put the name in. Then in, say, A5, use this formula: =query(Master!A2:Z,"Where A = '"&A5&"'") Where the list of names is in the Master list in column A. Hope that helps!
@MrKevharvey
Жыл бұрын
Great tutorial. Do you know of a way to create a drop down that only has options based on another cell in the same row. e.g. cell in column A has country filled in as text. Column b needs to be a drop down of cities only in that country. I'm trying to use this for ACC numbers showing up per patient for a physio in NZ. Great to see a fellow Kiwi on here too :)
@HashAliNZ
Жыл бұрын
Kia ora Kevin! There's a couple of ways - both aren't great. First, you could create a separate list of countries and corresponding cities. Similar to what I did in the video. The problem with this is there are hundreds of thousands, maybe millions, of cities, and even with a few tens of thousands your spreadsheet will slow down considerably. The second way is to find a website with a list of countries and then import using importhtml or importxml. However you'll be limited to the list of cities you can find online, and unless there's a repository somewhere you're unlikely to capture every country (even wikipedia doesn't have a great list!)
@vinaykumargorli9987
Жыл бұрын
Thank you for this information 🎉
@HashAliNZ
Жыл бұрын
My pleasure 😊
@mdhafizhasan755
Жыл бұрын
You guy is amazing. I have been searching for this one long since, and most of the videos are giving partial solutions. Your ones have a complete package. Thanks a lot
@SalmanSadiq
2 ай бұрын
Nice one. A small improvement in query function could be, instead of selecting one column at a time and nameing them 'drop down 1', 'drop down 2' etc, just select all like this "select * where .... " or select what you need "select A, D, G,H ... where ... " then just select new resultant column and map this range to next dropdown. Plus I picked up couple of productivity tips a) instead of freezing the row from menu, just drag thick border under row 1 to where you want to freeze b) instead of adding dropdown down from menu or right click just type @ drop
@joelencina6850
8 ай бұрын
this is an amazing video and very helpful. in your video you populated only 2nd row, how about the suceeding row? what is Query script for the 2nd and suceeding row?
@c.parizeault4793
Жыл бұрын
Hello, I made a dropdown list dependent on another one. However, when I change the value of the first list, the selection in my second list stays on my old selection. How to solve this problem and make the second list automatically update or delete itself when the value of the first list is changed? I see that an invalid message appears in yours, how to display the invalidity?
@maanNL
21 сағат бұрын
Why do i need to write ; instead of , ? Only with ; does the code work with me.
@MikeThackston
5 ай бұрын
I have an excel document that is for cabinet company. I have six tabs within the workbook. The first is the workbook, which is essently the customer pricing page. They have many options on this page to order what they would like. First, they enter the quantity in column A. Then, they choose which "Cabinet Series" they want (EconComm, Contractor, Premium, EcoGreen, Closets). Then they select the "Door Design" they want (about 10 options), then they select the "Door material" they want (30 options), then based on the prior items selected they should be able to talk to column "E" which is the "Cabinet Type" that corresponds, the select the "Cabinet Configuration" which corresponds, then enter the "Width, Height, Depth" to give them their price. I would be grateful for any ideas. Thanks.
@HashAliNZ
5 ай бұрын
New video coming out next week on how to do this 👍👍
@JoshuaAkonom
2 күн бұрын
I'm not getting any autofill option, so getting really jammed up.
@AJFulford84
7 ай бұрын
this video half solved my problem thank you very much for that, but i did manage to find another way to have multiple rows in another video kzitem.info/news/bejne/paF5uqmZhamcZ5w
@Ryuzanandfriends
3 ай бұрын
is it possible that we can copy the dropdown menu's of B2 and C2 shown at 4:44 into another sheet and achieve the same outcome? please help.
@DaniilBohatyr
7 ай бұрын
Hi, good video, but it hasn't solved my challenge. Is there a way to scale the first method and make the same conditions for a multiple rows for both drop-downs? I have a category and subcategory of the products, and I need each cell to display the same values. The second method doesn't work in this case, unfortunately.
@HashAliNZ
7 ай бұрын
There is multiple methods to do this, but my video on it isn't finished. My plan is to have it uploaded on April 1st. It's a bit too long to go into detail in a KZitem comment, unfortunately.
@ashley5352
11 ай бұрын
so the reason I cant seem to find a way to not have to create new lists for every single row is because you cant? How? that seems like a basic feature you should be able to do...
@TylerAlbers01
11 ай бұрын
i know right? And all of these people are fawning over this video, when it doesn't even show what it claims to show in the title. His answer to multiple dependent dropdowns on every row is to... just use one dropdown??? wtf?
@HashAliNZ
10 ай бұрын
It is possible, but I reserved this video for showing how to do it with any number of columns, up to Sheets' limit. If you want a dropdown in every column, that's been covered many times on KZitem so I created this different, unique method which is easily searchable and useable. Dependent dropdowns isn't a basic feature. It is in some programs like AppSheet using the _IsValid feature, so if you want that search for that. But since you're after multiple dropdowns I'll do two videos - one with scripts and another without. It'll be limited to a few columns because that's just not how this works. The other option is to use Excel's workaround for dependent dropdowns using named ranges.
@HashAliNZ
10 ай бұрын
@TylerAlbers01 "How To Create ENDLESS Dependent Drop-Down Lists in Google Sheets for Every Row" How To Create - yes, this is a tutorial. ENDLESS - Yeah, it's not really "endless". 18,726 dependencies and 50,000 characters in one cell, or about 400,000 rows. But if you're using dropdowns for 18k dependencies you've got other issues. Dependent Drop-Down Lists - Yes, one thing leads to another, leads to another, leads to another. The very definition of "Dependent" Google Sheets - Duh Every Row - Yeah... It works With all those people 'fawning' over the method, saying it's perhaps not what they were initially after but it works (and works well!), and only a few people finding an issue with it, one group certainly dominates another. I wonder who the problem might be?
@alexy6106
9 ай бұрын
Can you do the same in excel?
@shakhobiddinnakiev6767
2 күн бұрын
you cheated, but quite helpful
@BRBbass
Жыл бұрын
Thank you. I've been trying to do this for several hours and tried several tutorials but yours is the only one that has worked.
@HashAliNZ
Жыл бұрын
Happy to help! If there's anything else you need help with, just let me know
@gyankigudri
2 ай бұрын
❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤
@CarolynQui
Жыл бұрын
oops! I need to link these columns to the google form.
@viktoriiavitko7296
5 ай бұрын
Great tutorial! But what if you had cities in the rows instead of columns. Like F2: China, G2: Beijing, H2: Shanghai etc; F3: Japan, G3: Tokio and so on. Would that be possible to make such drop-downs without reformatting the categories Thank you!
@HashAliNZ
5 ай бұрын
You'd need a helper column using the transpose function to turn them from cols to rows
@RockAGoth
3 ай бұрын
So I’m making a database for a wardrobe with about 1500 items. The drop downs help me out greatly and some of the clothing categories have sub categories. Do I need to make a separate query for each item? At that point I’d be better off typing it out. Everything in the subcategory is just going off the one query we did.
@RockAGoth
3 ай бұрын
I think i figured it out I’ll just have multiple columns instead of one column of subcategories thanks!
@gaukala22
4 ай бұрын
Hi, I've watched this so many times!! I just have a small problem, when I create the query, it only show me one of the names in column G. If I change the value in the first dorp down menu the query sometimes changes to the first word on G matching the one in F. Not all the time and it doesn't show me all the results it should.. I don't know if I made myself clear or if you can help me with this
@HashAliNZ
4 ай бұрын
Sorry, I'm not quite sure what you mean. Feel free to send me an email with the spreadsheet link to themathlabnz@gmail.com and I can take a look
@dzilen
3 ай бұрын
How do you create multiple rows of a conditional drop-down function? NOBODY SEEMS TO KNOW HOW!!
@HashAliNZ
3 ай бұрын
The second half of the video shows how to have multiple rows, or you could check out this video: kzitem.info/news/bejne/t4eK2KuZZpd-iaA I've got a third method that uses script coming soon. It's just in the final stages of editing
@pseudoforce
3 ай бұрын
What if I want to have multiple roles with these drop downs? I can't just drag because then it copies the previous one.
@HashAliNZ
3 ай бұрын
The second half of the video shows how to have multiple rows, or you could check out this video: kzitem.info/news/bejne/t4eK2KuZZpd-iaA I've got a third method that uses script coming soon. It's just in the final stages of editing
@pseudoforce
3 ай бұрын
@@HashAliNZ thank you so much! I think my adhd kicked in 😅
@deymacias
3 ай бұрын
great video. Does anyone know how to reset the dependent drop-down list when the initial one is set to a different value?
@HashAliNZ
3 ай бұрын
Hey! This will require a script. My next video, which is almost done editing, is going to show exactly how to do this. Hopefully get it out in the next few days!
@johankallum
Жыл бұрын
This was truly amazing! This brought my spreadsheet to another level.😀
@LocsLuxeCurves
4 ай бұрын
Is there not a way to have multiple drop downs read from the same query formula?
@HashAliNZ
4 ай бұрын
No, not with this method. I've got a different method that will do it here: kzitem.info/news/bejne/t4eK2KuZZpd-iaAsi=94cfmVgjpsREebpE
@joeypelerine133
5 ай бұрын
Great video! Is there a way to create multiple drop downs as you continue to each column? EX: I choose a "Parent asset" in column A dropdown list. It would then populate in column B, multiple drop downs for every child asset associated with chosen Parent asset in from column A. The Child assets in Column B are now Parent assets to multiple drop downs in column C.
@joeypelerine133
5 ай бұрын
Continent Country Country Country City City City City Country Continent Hope this gives a better visual. Looking for every cell to be a drop down that expands as you keep going to the next column.
@HashAliNZ
5 ай бұрын
Absolutely. Check it out here: kzitem.info/news/bejne/t4eK2KuZZpd-iaAsi=Rvgy1bnk6pAiYZGU
@tomwoodcraft_gfx
3 ай бұрын
are there any heroes out there ? been struggling for an elegant solution....have a sheet with several drop down columns, im trying to make it simple for clients and other users of the doc to filter what is relevant to them, ideally without removing the other data, just re ordering it , for example a designer that just wants to see the tasks assigned to them , is it possible to create a dropdown where they select their name and it filters it ? drop-down or search boxes are great... basically anything that doesnt require any knowledge from the user, ANY help warmly welcomed
@HashAliNZ
3 ай бұрын
Hey Tom, there's a couple of ways in Google Sheets. One is to use Filter Views. I don't have a video specifically on this, but I show off the basics in the video about Structured Tables. The other option is to create a Looker Studio page that links directly to your Google Sheets data.
@tomwoodcraft_gfx
3 ай бұрын
@@HashAliNZ thank you!
@LocsLuxeCurves
4 ай бұрын
My question is I followed all of your steps and I was able to successfully have functioning drop down list, but the little red arrow still shows in the cell and it states, "Invalid: Input must fall within specified range" but the list are correct, what am I missing, why is that still there and will it cause issues later if I keep it?
@HashAliNZ
4 ай бұрын
Heya, This will only happen if the dropdown range doesn't include the value you've selected. It's hard to tell why this might be happening since you say your list is correct, so feel free to share the file with me and I can take a look.
@LocsLuxeCurves
4 ай бұрын
@@HashAliNZ thank you, I actually figured it out. I had to format the cells to plain text instead of automatic
@ecommerceimar
5 ай бұрын
why if I need this dynamic validation to all row? not only in 1 row?
@HashAliNZ
5 ай бұрын
It does apply to all rows..
@SIOSpawn
8 ай бұрын
please in the future paste the formulas in the description
@luizfernandosp
2 ай бұрын
the only problem in the traditional way, if you want to do the dropdown list in another row the query won't do anything because that list is using if only one cell is set. How to replicate to other rows?
@HashAliNZ
2 ай бұрын
That's when you use one of the other methods. Here's one: kzitem.info/news/bejne/xG6eu21_roVhiXY And here's another: kzitem.info/news/bejne/t4eK2KuZZpd-iaA
@luizfernandosp
2 ай бұрын
@@HashAliNZ I used the second method of this video, once is only for a monthly budget, nothing complex, but I understood the explanation of both videos with script and the other doing tab by tab, it's a big work if you need something more professional. I'm subscribing on your channel because I can find anything about Google sheets here hehhe, tks.
@RazaGhaniwala
Жыл бұрын
BEST
@MrDhiman
Жыл бұрын
Great work..... but how that 1st type of dependent dropdown work on the below let say 20 rows. I am unable to figure out kindly explain.
@HashAliNZ
Жыл бұрын
Hey Mr.Dhiman! The first method isn't really designed to work for many rows. That's where the second method dominates!
@Hermiel
8 ай бұрын
I use a very similar method for creating category structures for all kinds of applications. My method is a little more complex but gives me more flexibility for the kind of work I tend to do.
@Trade-Intuition
7 ай бұрын
wow ... the 1:00 time stamp is GOLD ... binge watching your videos on google sheets
@kennyrabbit70
Жыл бұрын
It'll save couple of extra column.
@HashAliNZ
10 ай бұрын
Absolutely! Nice, clean Sheet!
@mirzafarhan7883
Жыл бұрын
Dear Ali Thanks for sharing such valuable technic but i have a question since i am new to Google Sheets how would you select data from another sheet? I tried many times but i am getting error. If possible please share the way of doing it .
@pappafritto
10 ай бұрын
this is a good solution for making endless lists, but i am looking for something that can run an sql that depends on the cell on the left
@HashAliNZ
10 ай бұрын
Google Sheets has its own SQL language using the QUERY function. Referencing something within a query would look like this if it's text in the cell: =QUERY(A2:F, "Where C = '"&L2&'") or like this if is a number in the cell: =QUERY(A2:F, "Where C = "&L2&")
@pappafritto
9 ай бұрын
@@HashAliNZ thank you for the answer! I had discovered this function after a while
@hanxianhooi2199
Жыл бұрын
Hi It's amazing. On top of this, may I know how if I need it to be apply on every single row by using with multiple user to chosse the drop down in the same time ?
@teamcarevn
Жыл бұрын
Many thanks for your video. I have a little problem in the front row it works fine but from row A3 onwards it doesn't work. Please give me the solution. Thank you
@HashAliNZ
Жыл бұрын
Hiya! Which part doesn't work? Is it not showing the dropdown boxes or is it the split formula that's not working out? If you're using the first method, that's not really designed to work for multiple rows - that's where the second method dominates!
@KhripunovSA
3 ай бұрын
LOL, Yekaterinburg)))) you did you pretty good!
@quraanrecitation7811
5 ай бұрын
i need 2 dropdowns depending on 1 dropdown, for example i make 1 dropdown with iphone x, then next two dropdown should show depend on 1st dropdown
@HashAliNZ
5 ай бұрын
Right here, bud: kzitem.info/news/bejne/t4eK2KuZZpd-iaA
@quraanrecitation7811
5 ай бұрын
@@HashAliNZ not in google sheet, need it in excel
@HashAliNZ
5 ай бұрын
@@quraanrecitation7811 Cool, check out an Excel channel 👍🏼
@quraanrecitation7811
7 ай бұрын
can you make this in MS-Excel except google sheet
@thevaliantrebel4338
4 ай бұрын
Can i do this on excel ?
@HashAliNZ
4 ай бұрын
Not this version, but you can totally do dependent dropdown lists in Excel. I don't have a tutorial for that, unfortunately, because I do Google Sheets. But if you search for "Dependent Dropdown Lists in Excel" there are plenty of tutorials out there
@bambangwibi7322
Жыл бұрын
Why we need dependent list?
@HashAliNZ
10 ай бұрын
For selecting different items that belong in different categories. Generally for businesses or budgets
@MISMydaddy
9 ай бұрын
this is just for one row but ??
@HashAliNZ
5 ай бұрын
No this is for all rows..
@RebekahAdams-u4z
Жыл бұрын
Do you have advice for if the second drop down list is pulling more data than its supposed to. So for example, If I have Asia selected, and the 1st drop down list formula gives me Japan, china & Korea. (which is correct) But then the second drop down list for the cities once I select Japan is showing me Tokyo, Seoul, and Beijing. And not just Tokyo as it should be, in essence it's disregarding that I've selected one county. Any advice/ problem solving is appreciated!! I got it to work the first time but every other time I'm getting this glitch..
@HashAliNZ
Жыл бұрын
That is strange! Are you using the first or second method shown in the video?
Пікірлер: 250