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.
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
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.
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
for people who are trying to adopt this to use for labeling: you need to change the query so that it looks like =transpose(QUERY(B:C, "SELECT C WHERE B = '"&referenced_sheet!K2&"'", B1:C1)). In the formula, B1:C1 are your headers. Because if you don't use headers there is an issue with cutting the list in our dependent list by one on each following row until you are left with "N/A". That way, let's say you choose the labels "cats" from column C and you will have your options from column B "white, calico, red,.." on each row which is useful for labeling
@@HashAliNZ most people probably are unable to set it up, I have about 10 categories and 25 subcategories with more than 100 sub-subcategories and this just made my day 100x better haha!
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.
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.
This is amazing, I subscribed very quickly even though I don't need to do this. I can easily understand what your saying and follow on. I shall peruse your videos to see if I can find help for my simple problem.
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?
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
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?
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 :)
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!)
I havea video on multi select dropdowns but haven't yet incorporated dependent dropdowns into that. Might be something for me to mull over. Thanks for the thought!
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.
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 UA-cam comment, unfortunately.
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!❤
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(); } }
I've been needing these videos for a long time, and finally, I found you. However, I'm facing a limitation with the number of entries in data validation-I have 84,000 rows of data. How can I solve this issue? Could you help me? Many Thanks
Hi @salmansahuri35, I think this video might help you out: ua-cam.com/video/W1R5EKjwNHU/v-deo.html With this method, limitations should not be a problem as it doesn't use the data validation function.
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
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
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
@@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
Hi @@gulanman, 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
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.
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.
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.
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.
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.
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.
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
If I need multiple cells that dictate the dropdown menu options, I then have to create multiple queries so to generate different dropdown ranges (since each query function calls on only ONE specific cell as input)? Is there a way to generate multiple lines of dropdowns without having to create a new dropdown range for each?
Sure. Just reference a different dataset. But then you have to decide what controls which dataset is selected as the dropdowns. But that's the whole point of it being dependent dropdowns.
@@HashAliNZ I may not have asked my question clearly - I meant that if I were to create a similar dropdown pair in your sheet under the pair you created (B2,C2), then I would have to create another "Dropdown List" from which to pull the range from; i.e., for B3,C3 it would be "Dropdown List 3", B4,C4 = Dropdown List 4, B5,C5 = Dropdown List 5, etc... Is there a way to do it without being data-heavy like here? Without having to repeatedly generate another dropdown range manually? I still want them to be dependent, but I don't want to create a new range every time while still pulling the correct data based on the input.
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!
You might need to use some textjoins and splits to get that work efficiently. But there's a thousand ways to do things in Google Sheets, so I'd need more info for your case.
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 ?
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.
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.
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.
@@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..
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?
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.
@@HashAliNZ docs.google.com/spreadsheets/d/1olLzoriP-l7Of8d_WEX-dQWlrT1V_QhLyUexL1bXzYE/edit?usp=sharing USD → only plans wanting to get E3 working Thanks
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 .
The second half of the video shows how to have multiple rows, or you could check out this video: ua-cam.com/video/VQTrwc-eMVk/v-deo.html I've got a third method that uses script coming soon. It's just in the final stages of editing
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..
Your video is amazing! Congratulations for that!! But I need your help I already spent more than 3 hours trying to replicate this formula in a simple spreadsheet but seems like I’m not able to replicate the formula for other lines below, is that right? In your first exemple you use the B5 cell as reference, but when I’m try to use the drop-down on cell B3 the selection on C3 keeps being dependent on B2 instead of B3, could please help me?
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?
That's when you use one of the other methods. Here's one: ua-cam.com/video/c8hU9IuS0VA/v-deo.html And here's another: ua-cam.com/video/VQTrwc-eMVk/v-deo.html
@@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.
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
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? 🤷♂️
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: ua-cam.com/video/7mo4COng7Sg/v-deo.html
@@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. 🙏🙏🙏
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
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!
Hello! Thanks for your video-very helpful! I got it to work perfectly, however, I have a list with various inputs (rows) and each row has a different, let’s say, “Country” and “City.” When copying the formula down, values in the “City” drop-down don’t change since the formula you suggest is referenced to whatever the value is in B2 which is, for all intents and purposes, fixed. How can you make the list so that this value automatically changes as you select other “Countries” as you go down your list? I hope the question makes sense. Thanks!!!
Hi. I am still using excel (data validation - list - Indirect, because did not find a way to do: - two dependent drop downs - About 10 categories and around 3-10 subcategories for each categories - would need this for about 2000 rows. In google sheet only found how to do with one row Any suggestion how to achieve this?
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.
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
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.
That's fantastic to hear, Edward! Keep on learning and improving your skills!
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
wow ... the 1:00 time stamp is GOLD ... binge watching your videos on google sheets
Today is the day! Thank you, i finally fixed a huge problem in my spreadsheet with the help of your video. God bless you!
for people who are trying to adopt this to use for labeling: you need to change the query so that it looks like =transpose(QUERY(B:C, "SELECT C WHERE B = '"&referenced_sheet!K2&"'", B1:C1)). In the formula, B1:C1 are your headers. Because if you don't use headers there is an issue with cutting the list in our dependent list by one on each following row until you are left with "N/A". That way, let's say you choose the labels "cats" from column C and you will have your options from column B "white, calico, red,.." on each row which is useful for labeling
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.
Happy to help! If there's anything else you need help with, just let me know
thank you sir, i was loooking this for a long time
Thank you so much!!! Became an expert in 20 minutes after having lied in my resume that I was an expert on Google Sheets.
Fake it til you become it!
I have watched so many videos and yours is the only one that made sense to me. Thank you so much!!!
The last option is so simple but so useful! Thank you!
You're the best person to use an define humanitarian, for making a course like this free.
thanks Boss.
Watching from Nigeria.
Only one thing: YOU ARE A GENIUS .
Thanks a lot
Thanks so much!
This is what I needed for a long time
The second part of the video is a magical thing only you know Hash, AWESOME WORK!
I'm surprised how many people tell me it's a terrible system. I love it and how easy it is to search!
@@HashAliNZ most people probably are unable to set it up, I have about 10 categories and 25 subcategories with more than 100 sub-subcategories and this just made my day 100x better haha!
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.
New video coming out next week on how to do this 👍👍
i've tried to do this for hours ....... thanks for this video ! not all heroes wear capes !
This is the best video for Dependent Drop downs in google sheets. Kudos!
Thanks so much for the kind words!
Superb video, so glad I found Hash, smart guy :)
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.
Yeah ya gotta decide which method and tool is better for your particular data
This is amazing, I subscribed very quickly even though I don't need to do this.
I can easily understand what your saying and follow on.
I shall peruse your videos to see if I can find help for my simple problem.
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?
This is soo cool. Thank you. Subscribed!
This was so helpful and so easy. Thank you!
how can we do this if our data is in report format not tabular format?
This was truly amazing! This brought my spreadsheet to another level.😀
This is super helpful. Thank you for sharing this.
Thank you very much Hash. Do you have a list of short cuts for special characters like the arrow you used with Alt 26
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
This help me a lot! Thank you!!
Amazing, I love your instructions, very clear.
super helpful, thanks! I'm curious if I can do this by grabbing data from another spread sheet
you can do it with import range
this is the best solution, thanks a lot!
You're welcome. And if you're wanting the more traditional dependent dropdowns, that video is coming next week
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?
This is just what I need. Thank you.
Happy to help!
Love it. My problem is solved. Thanks.👍👍
Fantastic! There's also another method shown here if you want something different: ua-cam.com/video/VQTrwc-eMVk/v-deo.htmlsi=cG60UpXna8gYuoBF
Save my day. Thank you so much!
Thank you for this amazing explanation and video! :)
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 :)
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!)
Great explaination and through. How do you handle a dropdown with multiple selections?
I havea video on multi select dropdowns but haven't yet incorporated dependent dropdowns into that. Might be something for me to mull over. Thanks for the thought!
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.
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 UA-cam comment, unfortunately.
This video is great!
Thank you for sharing this. I need this so much at work.
You're very welcome!
Wow nice tutorial on drop downs. Thank Hash!
Thanks Dejan!
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!❤
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();
}
}
ohhh.... woooowwww thanks man.. problem solved!!! i will recommend you..
I had to give up Excel for Google Sheets for one simple feature. Searchable dropdowns. Excel is still clicking/scroll. Unreal.
There is a way to have searchable dropdowns in Excel, but oh man it's complicated!
Super helpful! thnx a million bro
Always happy to help, Rami!
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.
this is the best tutorial :)
Awesome, thank you!
Many Thanks, best video on the subject!
Glad it was helpful!
Dude your a life save
Thanks..It helped me a lot
Glad it helped
LOL, Yekaterinburg)))) you did you pretty good!
can you make this in MS-Excel except google sheet
I've been needing these videos for a long time, and finally, I found you. However, I'm facing a limitation with the number of entries in data validation-I have 84,000 rows of data. How can I solve this issue? Could you help me? Many Thanks
Hi @salmansahuri35, I think this video might help you out: ua-cam.com/video/W1R5EKjwNHU/v-deo.html
With this method, limitations should not be a problem as it doesn't use the data validation function.
Is there a way to sort by drop down status? I want "follow up" drop down status to be priority in my list.
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
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
@@HashAliNZ That's awesome thank you :) I will have a play about with that and see what I can do
This is what I'm looking for, but there's something I need to add that sheet link to the Google form. Please advise.
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
@@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
Hi @@gulanman, 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
Super helpful!
Glad it was helpful!
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.
I think i figured it out I’ll just have multiple columns instead of one column of subcategories thanks!
Great tutorial. Thank you very much
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.
thanks This made my work so much easy...
Perfect!
Thank you for this information 🎉
My pleasure 😊
Really helpful and good explanation. I subscribed your channel 😊
Thanks so much, Sajin!
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.
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.
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.
Absolutely. Check it out here: ua-cam.com/video/VQTrwc-eMVk/v-deo.htmlsi=Rvgy1bnk6pAiYZGU
please in the future paste the formulas in the description
This is amazing. Thank you for sharing. :)
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.
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
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
why if I need this dynamic validation to all row? not only in 1 row?
It does apply to all rows..
If I need multiple cells that dictate the dropdown menu options, I then have to create multiple queries so to generate different dropdown ranges (since each query function calls on only ONE specific cell as input)? Is there a way to generate multiple lines of dropdowns without having to create a new dropdown range for each?
Sure. Just reference a different dataset. But then you have to decide what controls which dataset is selected as the dropdowns. But that's the whole point of it being dependent dropdowns.
@@HashAliNZ I may not have asked my question clearly - I meant that if I were to create a similar dropdown pair in your sheet under the pair you created (B2,C2), then I would have to create another "Dropdown List" from which to pull the range from; i.e., for B3,C3 it would be "Dropdown List 3", B4,C4 = Dropdown List 4, B5,C5 = Dropdown List 5, etc... Is there a way to do it without being data-heavy like here? Without having to repeatedly generate another dropdown range manually? I still want them to be dependent, but I don't want to create a new range every time while still pulling the correct data based on the input.
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!
You'd need a helper column using the transpose function to turn them from cols to rows
What if you want to add a dropdown within a formula where you used index and matched to a dropdown?
You might need to use some textjoins and splits to get that work efficiently. But there's a thousand ways to do things in Google Sheets, so I'd need more info for your case.
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 ?
I Want to use on 100 line the same dependent drop down how i Can?
Great way, very creative, thank you!
You are so welcome!
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.
That sound complex! Can you share a copy of your spreadsheet and a mockup of your desired result? Share at themathlabnz@gmail.com
This is really cool
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.
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.
@@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..
@@gadd99 do you mean like a product name in one cell and a sku in the other?
Sadly, when accessing google tables from mobile phone, its not searching the row, only if you write starting with first letters of entire row
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?
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.
@@HashAliNZ thank you, I actually figured it out. I had to format the cells to plain text instead of automatic
I am trying to add if formulas in another coloumn based on city but am unable to capture the trigger city
any ideas please
Hi Peter, Can you share what you've got so far?
@@HashAliNZ docs.google.com/spreadsheets/d/1olLzoriP-l7Of8d_WEX-dQWlrT1V_QhLyUexL1bXzYE/edit?usp=sharing
USD → only plans wanting to get E3 working
Thanks
Hi Mate did you have any luck with what could be going on in this spreadsheet for it not to work.
@@HashAliNZ
@@petermarsh2174Can you share your spreadsheet? Feel free to email it to me at themathlabnz@gmail.com and I can take a look
hey mate. Do you have a demo of how to do the first method, but by using data from another sheet within the same file?
Here you go: docs.google.com/spreadsheets/d/1f_k0hC0cnS0QMcp7l8KLBBKShCXBy2o7oRLIMThuMR8/edit?usp=sharing
Why do i need to write ; instead of , ? Only with ; does the code work with me.
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 .
Is there not a way to have multiple drop downs read from the same query formula?
No, not with this method. I've got a different method that will do it here: ua-cam.com/video/VQTrwc-eMVk/v-deo.htmlsi=94cfmVgjpsREebpE
What if I want to have multiple roles with these drop downs? I can't just drag because then it copies the previous one.
The second half of the video shows how to have multiple rows, or you could check out this video: ua-cam.com/video/VQTrwc-eMVk/v-deo.html
I've got a third method that uses script coming soon. It's just in the final stages of editing
@@HashAliNZ thank you so much! I think my adhd kicked in 😅
Great video, you are Super Smart :)
Thank you so much, Cecile! I appreciate the super smart comment. I just see myself as experienced
Is it possible to use a combination of multiple dependent dropdown lists and query function?
Hmmm I've never tried, but I don't see why not!
i needed just the first dropdown list but i have to make a dropdown query for each vertical dropdown list, Understand ?
Sorry, I'm not quite sure what you mean. Can you expand a bit?
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..
That is strange! Are you using the first or second method shown in the video?
Your video is amazing! Congratulations for that!! But I need your help I already spent more than 3 hours trying to replicate this formula in a simple spreadsheet but seems like I’m not able to replicate the formula for other lines below, is that right? In your first exemple you use the B5 cell as reference, but when I’m try to use the drop-down on cell B3 the selection on C3 keeps being dependent on B2 instead of B3, could please help me?
Hi Alipio, Do you mean applying the dropdown list to lots of rows like at 11:20? The only thing that needs to be changed is the Apply To range
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?
That's when you use one of the other methods. Here's one: ua-cam.com/video/c8hU9IuS0VA/v-deo.html
And here's another: ua-cam.com/video/VQTrwc-eMVk/v-deo.html
@@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.
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.
Hey Mr.Dhiman! The first method isn't really designed to work for many rows. That's where the second method dominates!
Can i do this on excel ?
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
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? 🤷♂️
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: ua-cam.com/video/7mo4COng7Sg/v-deo.html
@@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. 🙏🙏🙏
@@martinjudd952 Well if you do make the shift, you know where to find me for help!
@@HashAliNZ ❤️ Yes I do!!
My son loves Kiwi’s, always so helpful 😉
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
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!
Hello! Thanks for your video-very helpful! I got it to work perfectly, however, I have a list with various inputs (rows) and each row has a different, let’s say, “Country” and “City.” When copying the formula down, values in the “City” drop-down don’t change since the formula you suggest is referenced to whatever the value is in B2 which is, for all intents and purposes, fixed. How can you make the list so that this value automatically changes as you select other “Countries” as you go down your list? I hope the question makes sense. Thanks!!!
Sorry I'm not sure I follow. Can you share a sample of your spreadsheet?
Hi. I am still using excel (data validation - list - Indirect, because did not find a way to do:
- two dependent drop downs
- About 10 categories and around 3-10 subcategories for each categories
- would need this for about 2000 rows. In google sheet only found how to do with one row
Any suggestion how to achieve this?
@@stefangiezendanner7047 I also want the same, Have you found the solution?