Grab the file I used in the video from here 👉 pages.xelplus.com/sheets-multiple-dependent-file GOOD NEWS! The invisible dollar sign issue is fixed! We can see the $ signs in data validation and we can remove them. So you can skip the part where I update each cell reference and just remove the dollar signs from the first dependent validation, and then copy the data validation down (Works like Excel now 😉)
Hi Leila, great tutorial! However I tried copying the data validation down and it did not work; it just kept referencing the same row I set as the source for my original data validation. Any ideas? This spreadsheet lives in a google suite account, not sure if that makes a difference or not
love the tutorial!, and I'm happy I stumbled on this. I've been milling over this issue for a while now, and I also had the issue where it wouldn't copy relatively, down that dependent's column. But, _that said_ *I found that the solution was to set your **_criteria_** value, then set the **_cell range_** by adding **_...:B(n)_** , with n being the last row of that dependent's column. this makes them all relative, however when you reopen **_data validation_** , the **_cell range_** resets, and is updated for each row down the column.* I did end up using a _transpose_ on an _indirect_ function, and some limited hidden columns (namedtables) between each of a dependent column. As I write this out, Im thinking about just moving them to a separate sheet... _mmmm_
Darn, I should have read the comments sooner :P. But thank you for this, I have been lookign for this for awhile!! I set the data validation for the first cell. Then edited the data validation, removing the $ next to the row number, then did the copy and paste special and it worked exactly as expected.
From all the videos I've browsed yours was the most honest especially on the limitations. Other videos dont talk about it and how to deal with it. Your video was easy to follow and provided the solution to my problem. Thank you.
It took me a total of 3 hours to actually create a dependant dropdown, and only 10 minutes after stumbling upon your video! Thanks! I wish I found your video first!
I found an easier way to update all the formulas, instead of manually typing each number into the data validation. Basically, if you save the data validation and then go back, the $'s pop up and you can delete them. This worked for me, not sure if it will work for others. Instructions below. (Edit: Clearer instructions) At 5:42, she talks about invisible dollar signs stopping us from copying the data validation formula all the way down on the "Dep. List" sheet for row B. I followed her instructions from 6:00 to 6:15, and then I copied ONE cell only (unlike how she does all of them at 6:35) and pasted it into cell B4. I reopened the Data Validation for B4 and I could see all the $ signs. I manually deleted each one, and saved. NOTE: Make sure that you click on the text itself when erasing the $s-- do not click on the little box that looks like four squares that says "Select Data Range." If you click on that, it makes a new pop-up window, and it puts the $s back every time. Very frustrating if you keep deleting them. Then I copied the B4 formula all the way down, and it automatically changed all the numbers. I checked each cell; they all worked right. It was much faster than what she did from 6:44 to 6:51. I'm guessing that the "invisible dollar signs" are actually just auto-filled behind your back -- you see them if you reopen the formulas. Hope this helped someone!
Thank you a lot, to both of you! The video and this comment helped me solve my problem and create dependable dropdowns for a 3000rows table! At one point I was almost ready to start learning how to write a script, because I really wanted to have this feature. You've made it so much easier for me!
Oh my god!!! IT WORKED!!! Now I have several rows with independent dropdowns based on their own category. And I can have a thousand with no issue at all!!! I will bring some other frustrated people from other videos to keep helping!! Amazing!!!
Hi Leila, Google now lets you create a drop-down using "Relative cell referencing" like you created using the range "Master!F4:Z4 and now you can drag the drop-down down and the range will change for each row without having to "update" each data validation. When you create the drop-downs for the "Categories" in column A then make sure the first one uses "Absolute cell referencing" so that when you copy or drag the drop-down down into the cells below the "cell referencing" will be locked and not change. But when you create the "dependent drop-downs" in column B then create the first drop-down and then look back at it and make sure the "cell referencing" is set to "relative" by removing the $ signs. Then copy or drag down that drop-down to the cells below. The drop-downs will change without having to update each one. James :)
Do you know if this still works? I am trying to create a budget sheet with dependent drop-down categories and the $ are not visible. Wanted to see if this was true for other people 2 years since your comment. Thanks!!!
@@dillonmears6696 See my replay below and select the cell that has your dropdown and click "Data Validation" and then remove the $ signs from the rule and then drag the dropdown down and each range for that dropdown will change because of its "Relative" reference. You can click any of the dropdowns and then view its rule to see that the range has change for that particular row.
Hi, I have a suggestion. After copy-pasting the formula for data validation, instead of manually changing the cell row number in the last step - you may just open the data validation formula from any one of the pasted formulas (say 5th row). Remove the $ sign next to the cell number (keep column frozen) - for example: Sheet1!$K5:$N5 (originally it was Sheet1!$K$5:$N$5). Now you will get a popup asking if you want to make the same change to all the cells - and viola - it changes to respective row numbers. No need to manually change each and every formula.
I think number shouldn't matter for her. It mean it shouldn't affect her if 1M+ don't want to learn these tricks. She is changing lives, she is putting a huge positive impact on the careers of the people who are interested. I learned and progressed a lot by watching her videos. Thank you very much madam
Thanks, very usefull and clear. It worked but then I encountered a limitation, if I add a row in the middle of other rows with information filled in, it will pick the dropdown of above, and then it doesn't work anymore. In your example, if i have Game selected, and then I add a row and above and under it is game selected, but this new row is utility i want to select, then the second dropdown will get stuck at game dropdown. Would you have a way to fix this?
No sorry, we don't have a video for that. But Google fixed the invisible dollar sign issue! So, you can just remove the dollar signs from the first dependent validation, and then copy the data validation down.
Question if you do have a lot of drop downs to change do you have a different video that describes that complicated formula using what you called "abscript" ?
I'm not sure if it's a Google Sheet Update, but now you can select all the rows you want to have the dependent data validation on, and then on the lookup range where you refer to your data prep, you can remove the dollar signs. You don't have to do the manual changing of the data validation formula anymore.
Very Well, thanks! please explaine is it possible to have the content of the drop-down list hosted outside of the worksheet? In practice: I would like to distribute a worksheet which will contain a drop-down list, but I would like to have the controle of the items of the drop-down in case of an update without needing to access all the distributes worksheets one by one. Is it possible? I don't know!!! 😅
The dollars are there now to be able to edit in data validation, if you do a single dependant drop down and remove the dollar you can then drag the fill handle to fill the rest of the range.
I attempted another way to create the multiple dependent drop down lists, but I am stuck not being able to sort my sheet without breaking the formula. Before I even attempt this way, is it possible to do that? I'd be sorting by Column A, which is a date, and is not one of the drop down columns.
Leila, I have a large database for Condition 2 and Condition 3. It's obvious that I won't be able to have that many columns to organise my data that would be ever expanding. Is there a solution for this?
But drawback is it also increases rows in prep set. Is there not way that we only have one row from prep which will update according to value selected from main dropdown?
Hello. How can I create a dependant dropdown list for 500+ rows. I need to add more rows as time passes.. Also, if I use a filter in my master page, the dropdown list doesnt work properly anymore
Great, but I wonder if there is any other way of doing this without those multiple prep rows. I have a sheet with 12 tabs (one for each month), and I use them to control my finances, and I add rows on a daily basis for every expense... Each tab have more than a hundred rows, and I'd like to have this kind of dependent drop-down to categorize each row, but I couldn't create a "prep row" for each expense.... How could I do that?
Actually you can put formulas in Google sheets validations. And the best trick is to make name range of cells listed below each category name. So under Productivity you create a name range called "Productivity" and so on. Then you can create a formula in data validation associating the category with the name range. Lots easier and try it out. Great tutotial, just trying to help.
To add, the function to use is the indirect function. Using a helper column instead of inside of the validation is much easier. It eliminates having to change cell references.
Thanks for your input. Agree - name manager is an indirect way to keep formulas. The solution I originally tried with name manager though always worked for the last dependent drop-down. But I couldn't go back and revise a a previous drop-down. If you have a solution for this with name manager I'd love to know.
Hello! Your previous video on dependent dropdown was very helpful! But how would I formulate the multiple dependent dropdown list if the master database is in a tabular format? Tried using the method you detailed here but I keep getting #N/A return. Thanks!
Hi Leila! This video was so helpful! Quick question for you (or the group) Since I am using the chip drop down style and love to color code, do you know if there is a way to automatically have my dependent drop down follow the same colors as my first list? For example, if the Productivity division was red, then I would want all the productivity apps to also come up as red in my second dropdown. And then if I switched to the Utility division (which is blue), I'd want all those apps to show up as blue. Right now even if I manually change the colors of the app drop down once I've picked a division, they revert back to gray if I toggle the first drop down and the color clears out. I'm trying to avoid manually setting the color after if settled on the first drop down category. Not sure if its possible or if I made sense but hopefully someone out there has a solution!
Is there a good way to use this method if the drop down lists are part of a list of data with filters at the top? Sorting the list seems to break the conditional drop down.
I found that if I created an =index formula in a separate sheet when it came to making the data validation, you could click on the cell - data validation - select from range - then highlight the whole row or column whatever you choose to use, this speeds up the process of having to change the location of each dropdown menu. if you first make the index formula, after you have dragged that down/across the row/column, then you insert a cell to the left and to the top for your headings and description, google sheets will automatically adjust your formula so your titles/description will not appear in your lists
Hi Leila, Could you explain how to do this if the Master Data categories (in your case; Productivity, Game, Utility) are vertical and not horizontal. My categories are set vertically in column 1 with the data (in your case; Games) in column 2.
Awesome tutorials! I was SO glad to discover that dragging these vlaidations down now works. I have a big data set to work on (auto categorisation of bank transactions) and this has made a drop down list style of data entry possible.
I've been looking for a solution for having multiple dependent drop down list for google sheet for like years! thank you so much Leilaaa! You saved me but there is this problem that i want this for lots of rows like thousands :( is there any solution for this number instead of editing data validation for each row???
Hi Leila, when I fellow your video until 4:36, there is an error that "Array result was not expanded because it would overwrite data in G3." Do you know any way to fix it?
Awesome video, and with the fix it's even better... but my issue is I'd like to be able to sort by a column with a date, and if I sort the range it gets messed up because of the hidden tables. Any suggestions?
@Leila the invisible $ problem in Google sheet can be solved by putting the $ sign for the Data Validation you have done for the first row and then remove the $ and save the Data Validation. Now if you drag down the Data Validation it will automatically extend the selection. I have tried it out and it works
hi.. tq for the tutorial. I just wonder, if I want to create multiple dependent dropdown list in multilple spreadsheets, with the same data/same source, can it be done?
Is there a way that your keyboard automatically switch to numerical keyboard when searching? Because I have a list with numbers and when searching I can type in all characters like letters, etc. Instead I quicky want to type in a number. Is that possible on when I am on my iPhone?
Can anyone help me with a problem? Using this method, if I sort column A alphabetically later on, like say after I add another entry to the sheet, column B doesn't display the correct dropdown menus. How can I fix this?
Love your videos. You explain things very clearly with good examples. Can a 3rd column be created and linked to the 2nd dynamic dropdown, creating a 3rd choice based on the second choice?
Followed it all well enough and made the dynamic multiple list but when trying to create another same sheet different area the formula isn't working anymore on new lists of the same options. Any help?
I know it has been a while since this video was posted but this is the closest solution I've found to a problem I've been trying to solve for over a year, so I have to ask just in case someone answers - What if I need to have 3, or more, columns? For instance - Column A is a drop down populated from a data set Column B is a drop down populated based on the selection in Column A Column C is static display, or a drop down, based on the selections in both Column A and Column B In this example Column C can not strictly be determined based on Column Bs selection because the selection in Column A may alter the meaning of the selection in Column B, affecting the final output in Colum C I hope that explanation is clear enough for someone to be able to help me finally resolve this issue. Thank you for the tutorial!
Hi! I want to apply Multiple Dependent drop-downs, but the range is more than 2000 cells. How am I supposed to do that? Do u have a video on that? Please help((
Thank you so much for this video. That´s exactly what I need often. There is another question. Is it possible to use multiple dependent dynamic drop-down list on one sheet if i have my source/input data on the other sheet? It would be nice to solv this problem because the way you explanin in your video doesn´t work.
Thanks for the video, it is working for me! However, I have an issue whenever I sort my data by another column that do not have a drop down list. My second drop down list gets wrong as its data validation is moved. Anyone experiencing similar issue?
To drag down all the data validation relatively to the following cell with cell number sequence, just remove the "$" then drag down. Ex: ='DATA VALID'!$G3:$Z3 just remove the "$". Working in Google sheet
No, fortunately Google fixed the issue with the invisible dollar sign in the meantime! We can now see the $ signs in data validation and we can remove them. So you can skip the part where I update each cell reference. Just remove the dollar signs from the first dependent validation, and then copy the data validation down. Done! 😊
Was just recommended here by you. Great video. Do you think it is possible to use this for something like a budget for entering in expenses? In this case, there would be an unknown amount of potential expenses each month that would also potentially reoccur every month. I’m thinking you could do this, but it would be highly inefficient as you would end up using so many extra sheets worth of date prep categories for every month. Is this accurate? I’ve found a work around for this, but this would be a nice aesthetic addition. Quality content as usual. Thank you for the work you put into these videos.
I have been trying to do this, but cannot find a simpler way, especially on the part of preparing master data for potential 100+ items of expenses. Let me know if someone find an answer for this!
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
Hi, It was very useful, I have a problem as below; An agent is filling a form under name of a customer few days ago. So, I need to excel show the report, if employee X process form Y on date Z, system show entire ROW report, else a blank row.
Question I am looking a way to create chart and I need the chart to use the color in the table cells basic thee color I am using green, yellow and red. How can I get this to work?
two years after the creation of this its still helpful. and i'm glad i spotted the comment about the dollar sign issue being fixed.i have 1000 cells o_o
I'm curious, is there a way to cause the second dropdown list to default to an option within the parameters of the first? In your example in A3 you have Game selected and, in B3, Five Labs; the issue I'm having is that, using your example, if I were to switch that A3 cell to Productivity it would show an error because Five Labs would still be the input in B3. Is there someway to cause the second dropdown list to automatically default to the first available valid option of the parameter set in the first drop down list, this being WenCal in your example. Or will you always have to manually go in and change something?
I really like your channel and love the way you explain things. I'm great at following directions, but not so great at figuring out formulas to begin with. I have a request. Maybe you've already done a video about it and I missed it? So, here's what I'd like to be able to do: a drop down menu to select a lesson objective. Once the lesson objective is selected, the cells beneath that objective will populate with the entire lesson plan. I can visualize the set up, I think, but don't know the right formulas to use.
Still can not be used for inputs into an infinite database. I can imagine bulding something like creating a specific sheet for the data preparation, transform the formulas to Array kind, and play with the ranges between sheets. Does it worth it?
Grab the file I used in the video from here 👉 pages.xelplus.com/sheets-multiple-dependent-file
GOOD NEWS! The invisible dollar sign issue is fixed! We can see the $ signs in data validation and we can remove them. So you can skip the part where I update each cell reference and just remove the dollar signs from the first dependent validation, and then copy the data validation down (Works like Excel now 😉)
Hi Leila, great tutorial! However I tried copying the data validation down and it did not work; it just kept referencing the same row I set as the source for my original data validation. Any ideas? This spreadsheet lives in a google suite account, not sure if that makes a difference or not
love the tutorial!, and I'm happy I stumbled on this. I've been milling over this issue for a while now, and I also had the issue where it wouldn't copy relatively, down that dependent's column. But, _that said_ *I found that the solution was to set your **_criteria_** value, then set the **_cell range_** by adding **_...:B(n)_** , with n being the last row of that dependent's column. this makes them all relative, however when you reopen **_data validation_** , the **_cell range_** resets, and is updated for each row down the column.* I did end up using a _transpose_ on an _indirect_ function, and some limited hidden columns (namedtables) between each of a dependent column. As I write this out, Im thinking about just moving them to a separate sheet... _mmmm_
Also does not work for me!
Darn, I should have read the comments sooner :P. But thank you for this, I have been lookign for this for awhile!!
I set the data validation for the first cell. Then edited the data validation, removing the $ next to the row number, then did the copy and paste special and it worked exactly as expected.
Hi Leila, if I have 100 over rows is there any quick solution to it instead of updating each row like at 6:50 on wards?
From all the videos I've browsed yours was the most honest especially on the limitations. Other videos dont talk about it and how to deal with it. Your video was easy to follow and provided the solution to my problem. Thank you.
It took me a total of 3 hours to actually create a dependant dropdown, and only 10 minutes after stumbling upon your video! Thanks! I wish I found your video first!
I found an easier way to update all the formulas, instead of manually typing each number into the data validation. Basically, if you save the data validation and then go back, the $'s pop up and you can delete them. This worked for me, not sure if it will work for others. Instructions below. (Edit: Clearer instructions)
At 5:42, she talks about invisible dollar signs stopping us from copying the data validation formula all the way down on the "Dep. List" sheet for row B. I followed her instructions from 6:00 to 6:15, and then I copied ONE cell only (unlike how she does all of them at 6:35) and pasted it into cell B4. I reopened the Data Validation for B4 and I could see all the $ signs. I manually deleted each one, and saved.
NOTE: Make sure that you click on the text itself when erasing the $s-- do not click on the little box that looks like four squares that says "Select Data Range." If you click on that, it makes a new pop-up window, and it puts the $s back every time. Very frustrating if you keep deleting them.
Then I copied the B4 formula all the way down, and it automatically changed all the numbers. I checked each cell; they all worked right. It was much faster than what she did from 6:44 to 6:51. I'm guessing that the "invisible dollar signs" are actually just auto-filled behind your back -- you see them if you reopen the formulas.
Hope this helped someone!
I just checked. The invisible dollar sign issue is fixed! We can see the $ signs. YAY! This makes it all so much easier.
Thank you a lot, to both of you! The video and this comment helped me solve my problem and create dependable dropdowns for a 3000rows table! At one point I was almost ready to start learning how to write a script, because I really wanted to have this feature. You've made it so much easier for me!
Oh my god!!! IT WORKED!!! Now I have several rows with independent dropdowns based on their own category. And I can have a thousand with no issue at all!!! I will bring some other frustrated people from other videos to keep helping!! Amazing!!!
Yes! It worked! Thank you so much!
THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!! I've been trying to solve this for literally years.
Hi Leila,
You can remove the $ sign from validation and copy-paste, it will change the range as expected, which can be used for huge data set.
Yes, apparently this changed since I recorded the video. Thanks for the feedback!
Thank you. I'd appreciate your comments.
Please tell me how to do this 😂
Thank you so much for this comment
Thanks a million 🤩
Hi Leila,
Google now lets you create a drop-down using "Relative cell referencing" like you created using the range "Master!F4:Z4 and now you can drag the drop-down down and the range will change for each row without having to "update" each data validation.
When you create the drop-downs for the "Categories" in column A then make sure the first one uses "Absolute cell referencing" so that when you copy or drag the drop-down down into the cells below the "cell referencing" will be locked and not change.
But when you create the "dependent drop-downs" in column B then create the first drop-down and then look back at it and make sure the "cell referencing" is set to "relative" by removing the $ signs. Then copy or drag down that drop-down to the cells below. The drop-downs will change without having to update each one.
James :)
great , it works for me thank you
Do you know if this still works? I am trying to create a budget sheet with dependent drop-down categories and the $ are not visible. Wanted to see if this was true for other people 2 years since your comment. Thanks!!!
@@dillonmears6696 See my replay below and select the cell that has your dropdown and click "Data Validation" and then remove the $ signs from the rule and then drag the dropdown down and each range for that dropdown will change because of its "Relative" reference. You can click any of the dropdowns and then view its rule to see that the range has change for that particular row.
Hello Sir how come if i add new items on my options it wont add up to my current dropdowns? thank you.
Hi, I have a suggestion. After copy-pasting the formula for data validation, instead of manually changing the cell row number in the last step - you may just open the data validation formula from any one of the pasted formulas (say 5th row). Remove the $ sign next to the cell number (keep column frozen) - for example: Sheet1!$K5:$N5 (originally it was Sheet1!$K$5:$N$5). Now you will get a popup asking if you want to make the same change to all the cells - and viola - it changes to respective row numbers. No need to manually change each and every formula.
This was so helpful thank you!
You honestly deserve more than 1 million subscribers.. Let's all make that happen
Wow, thank you!
I think number shouldn't matter for her. It mean it shouldn't affect her if 1M+ don't want to learn these tricks. She is changing lives, she is putting a huge positive impact on the careers of the people who are interested. I learned and progressed a lot by watching her videos. Thank you very much madam
Thanks the video!
6:42 Should I change every B5:B14 for myself, not automatically?
Leila, I like the way u speak out your English .. It's, favourable for us all. Even here in Africa
This is brilliant, I'm just starting to learn and there's no way I could have put all those formulas together. Thank you so much!!
A huge thank you for these guides! They are really easy to follow!
This is awesome! Thanks for sharing this tutorial on drop-down lists :)
My pleasure :)
Hey Leila, how can I create a drop down from different ranges? Could you please help me out with the formula? Thank you in advance.
A channel I suggest often as an example to be a best youtuber.
Wow, thanks
Thanks, very usefull and clear. It worked but then I encountered a limitation, if I add a row in the middle of other rows with information filled in, it will pick the dropdown of above, and then it doesn't work anymore. In your example, if i have Game selected, and then I add a row and above and under it is game selected, but this new row is utility i want to select, then the second dropdown will get stuck at game dropdown. Would you have a way to fix this?
Hi, Do you have a video for creating multiple Dependent Drop Down for the entire sheet may be with App Script
No sorry, we don't have a video for that. But Google fixed the invisible dollar sign issue! So, you can just remove the dollar signs from the first dependent validation, and then copy the data validation down.
@@LeilaGharani Thanks
this video saved my life
This is lifesaver!! I watched 10 other videos and I was going crazy. And you made it super easy. Thank you, thank you, thank you
Glad it helped, Nika!
Omg THANK YOU!!!! I have been stuck on this for hours! Thought I was going to have to do the indirect function for every single row hahaha.
Glad it helped!
Question if you do have a lot of drop downs to change do you have a different video that describes that complicated formula using what you called "abscript" ?
What do I do if I want to add rows in the call out lists? The data prep doesnt automatically update and I get errors on some cells.
I'm not sure if it's a Google Sheet Update, but now you can select all the rows you want to have the dependent data validation on, and then on the lookup range where you refer to your data prep, you can remove the dollar signs. You don't have to do the manual changing of the data validation formula anymore.
Very Well, thanks! please explaine is it possible to have the content of the drop-down list hosted outside of the worksheet? In practice: I would like to distribute a worksheet which will contain a drop-down list, but I would like to have the controle of the items of the drop-down in case of an update without needing to access all the distributes worksheets one by one. Is it possible? I don't know!!! 😅
The dollars are there now to be able to edit in data validation, if you do a single dependant drop down and remove the dollar you can then drag the fill handle to fill the rest of the range.
I attempted another way to create the multiple dependent drop down lists, but I am stuck not being able to sort my sheet without breaking the formula. Before I even attempt this way, is it possible to do that? I'd be sorting by Column A, which is a date, and is not one of the drop down columns.
Each and every video of this playlist, is so much informative, thanks a lot mam, huge respect for you. Love from INDIA.
Leila, I have a large database for Condition 2 and Condition 3. It's obvious that I won't be able to have that many columns to organise my data that would be ever expanding. Is there a solution for this?
But drawback is it also increases rows in prep set. Is there not way that we only have one row from prep which will update according to value selected from main dropdown?
Hello. How can I create a dependant dropdown list for 500+ rows. I need to add more rows as time passes.. Also, if I use a filter in my master page, the dropdown list doesnt work properly anymore
Great, but I wonder if there is any other way of doing this without those multiple prep rows. I have a sheet with 12 tabs (one for each month), and I use them to control my finances, and I add rows on a daily basis for every expense... Each tab have more than a hundred rows, and I'd like to have this kind of dependent drop-down to categorize each row, but I couldn't create a "prep row" for each expense.... How could I do that?
Used Query condition.
@@hendragunawan3593 Im having a similar problem, any link for recommendations?
Actually you can put formulas in Google sheets validations. And the best trick is to make name range of cells listed below each category name. So under Productivity you create a name range called "Productivity" and so on. Then you can create a formula in data validation associating the category with the name range. Lots easier and try it out. Great tutotial, just trying to help.
To add, the function to use is the indirect function. Using a helper column instead of inside of the validation is much easier. It eliminates having to change cell references.
Thanks for your input. Agree - name manager is an indirect way to keep formulas. The solution I originally tried with name manager though always worked for the last dependent drop-down. But I couldn't go back and revise a a previous drop-down. If you have a solution for this with name manager I'd love to know.
how do i do that please
Great Video. How would you do this if your data is in tabular format?
Is there a way to pull the information on "notes" or "comments" linked to the extracted column? I really liked your video. Thank you so much.
what is the best way if want to do the same thing but over around 500 lines, do i just do it for every row or is there a better way?
You are an absolute life saver - I cannot thank you enough for this!
Hello! Your previous video on dependent dropdown was very helpful! But how would I formulate the multiple dependent dropdown list if the master database is in a tabular format? Tried using the method you detailed here but I keep getting #N/A return. Thanks!
Almost 4 years later, and while they did fix the default hidden absolute references, this is still the only working way I have found to do this.
Thanks go the update
Hi Leila! This video was so helpful!
Quick question for you (or the group) Since I am using the chip drop down style and love to color code, do you know if there is a way to automatically have my dependent drop down follow the same colors as my first list?
For example, if the Productivity division was red, then I would want all the productivity apps to also come up as red in my second dropdown. And then if I switched to the Utility division (which is blue), I'd want all those apps to show up as blue. Right now even if I manually change the colors of the app drop down once I've picked a division, they revert back to gray if I toggle the first drop down and the color clears out. I'm trying to avoid manually setting the color after if settled on the first drop down category.
Not sure if its possible or if I made sense but hopefully someone out there has a solution!
If you go into the second data validation, you can actually remove the $ and simply copy and paste the validation. Thanks for the tutorial!
Is there a good way to use this method if the drop down lists are part of a list of data with filters at the top? Sorting the list seems to break the conditional drop down.
I found that if I created an =index formula in a separate sheet when it came to making the data validation, you could click on the cell - data validation - select from range - then highlight the whole row or column whatever you choose to use, this speeds up the process of having to change the location of each dropdown menu. if you first make the index formula, after you have dragged that down/across the row/column, then you insert a cell to the left and to the top for your headings and description, google sheets will automatically adjust your formula so your titles/description will not appear in your lists
Fantastic video! I'm interested in Appscripts, do you have videos or courses on that. Congratulations & thank you
its really helpful at the start, but how if the row is so many? do you have any solution on this?
Hi Leila,
Could you explain how to do this if the Master Data categories (in your case; Productivity, Game, Utility) are vertical and not horizontal. My categories are set vertically in column 1 with the data (in your case; Games) in column 2.
Awesome tutorials! I was SO glad to discover that dragging these vlaidations down now works. I have a big data set to work on (auto categorisation of bank transactions) and this has made a drop down list style of data entry possible.
Thanks @Leila! Is there a way to have multiple columns into the Drop down List please?
Example:
[Col #1] Employee Code | [Col #2] Employee Full Name
I've been looking for a solution for having multiple dependent drop down list for google sheet for like years! thank you so much Leilaaa! You saved me but there is this problem that i want this for lots of rows like thousands :( is there any solution for this number instead of editing data validation for each row???
Yes can someone answer this question. I need help for our tracker. thank you
Thank you, but what if I have a 1000 lines in data validation table .. ??!
Hi Leila, when I fellow your video until 4:36, there is an error that "Array result was not expanded because it would overwrite data in G3." Do you know any way to fix it?
Awesome video, and with the fix it's even better... but my issue is I'd like to be able to sort by a column with a date, and if I sort the range it gets messed up because of the hidden tables. Any suggestions?
Thank you again...really appreciate your videos
@Leila the invisible $ problem in Google sheet can be solved by putting the $ sign for the Data Validation you have done for the first row and then remove the $ and save the Data Validation.
Now if you drag down the Data Validation it will automatically extend the selection. I have tried it out and it works
So i need to change the reference for each row. This is a manual task. So how to approach this problem using google app script.......
this solves my current problem, thank you so much!!!
Glad it helped!
I would like this but on a table. To have different row with Productivity, Games, etc, Is it possible?
extremely helpful video
hi.. tq for the tutorial. I just wonder, if I want to create multiple dependent dropdown list in multilple spreadsheets, with the same data/same source, can it be done?
Is there a way that your keyboard automatically switch to numerical keyboard when searching? Because I have a list with numbers and when searching I can type in all characters like letters, etc. Instead I quicky want to type in a number. Is that possible on when I am on my iPhone?
Can i do the same thing to import it to google form ? Or you have another way? Thanks
Can anyone help me with a problem? Using this method, if I sort column A alphabetically later on, like say after I add another entry to the sheet, column B doesn't display the correct dropdown menus. How can I fix this?
I am also experiencing the same problem, have you found a solution to it?
@@JohnHealthCoach Exact same issue!
Very Helpful... Really appreciated 🎉😍😘
how can you apply the modern coloured chips in this multiple depandant list
Love your videos. You explain things very clearly with good examples. Can a 3rd column be created and linked to the 2nd dynamic dropdown, creating a 3rd choice based on the second choice?
Not sure if you still need to know but yes, using the same instructions you can make another conditional dropdown in addition!
As great as your skills are, your personality is just as great... what a combination! thank you Miss. Leila G.
Thank you so much, Ted!
can it be multi select also in both the dropdown?
Very very Helpful Thank You!
Thanks Leila, you are awesome!
Followed it all well enough and made the dynamic multiple list but when trying to create another same sheet different area the formula isn't working anymore on new lists of the same options. Any help?
Good Leila, i was searching this function only
useful
I know it has been a while since this video was posted but this is the closest solution I've found to a problem I've been trying to solve for over a year, so I have to ask just in case someone answers -
What if I need to have 3, or more, columns?
For instance -
Column A is a drop down populated from a data set
Column B is a drop down populated based on the selection in Column A
Column C is static display, or a drop down, based on the selections in both Column A and Column B
In this example Column C can not strictly be determined based on Column Bs selection because the selection in Column A may alter the meaning of the selection in Column B, affecting the final output in Colum C
I hope that explanation is clear enough for someone to be able to help me finally resolve this issue.
Thank you for the tutorial!
Hi! I want to apply Multiple Dependent drop-downs, but the range is more than 2000 cells. How am I supposed to do that? Do u have a video on that?
Please help((
Same here. Looks like it's possible to do in Excel quite easily (with a long formula but just once) but I cannot manage to do it with Google Sheets
Thank you so much for this video. That´s exactly what I need often.
There is another question. Is it possible to use multiple dependent dynamic drop-down list on one sheet if i have my source/input data on the other sheet?
It would be nice to solv this problem because the way you explanin in your video doesn´t work.
Thanks for the video, it is working for me! However, I have an issue whenever I sort my data by another column that do not have a drop down list. My second drop down list gets wrong as its data validation is moved. Anyone experiencing similar issue?
I'm having this issue! Any fix?
Is it possible to make the dependent dropdown dependent on two columns?
GOOD ONE!
I'm about to a dropdown lists tutorial. We're thinking alike. 🤗💡
Thanks Oz! Looking forward to seeing your take on it.
Hi Leila,
I have 1500 rows! while applying the index formula & transpose how shall i do it for all rows now?
To drag down all the data validation relatively to the following cell with cell number sequence, just remove the "$" then drag down.
Ex: ='DATA VALID'!$G3:$Z3 just remove the "$".
Working in Google sheet
This saved my day! Thanks a lot :)
damn, watch to the end all excited only to realize i'm that one who needs this to work on thousands of rows
No, fortunately Google fixed the issue with the invisible dollar sign in the meantime! We can now see the $ signs in data validation and we can remove them. So you can skip the part where I update each cell reference. Just remove the dollar signs from the first dependent validation, and then copy the data validation down. Done! 😊
@@LeilaGharani thank you! I landed on this video again today to give the concept another go. I am oddly excited haha
What happens if i created a formula using xlookup with my office 2019 and send that Excel file to my friend who has office 2007? Will it work?
Xlookup will Not work bcs when function not exist in ms2007 then how can u run??
@@lkumar2727 thanks alot
I see Leila - I press Like.
Was just recommended here by you. Great video. Do you think it is possible to use this for something like a budget for entering in expenses? In this case, there would be an unknown amount of potential expenses each month that would also potentially reoccur every month. I’m thinking you could do this, but it would be highly inefficient as you would end up using so many extra sheets worth of date prep categories for every month. Is this accurate? I’ve found a work around for this, but this would be a nice aesthetic addition.
Quality content as usual. Thank you for the work you put into these videos.
I have been trying to do this, but cannot find a simpler way, especially on the part of preparing master data for potential 100+ items of expenses. Let me know if someone find an answer for this!
For drag validation go to first row remove $ sign from value then copy it and paste through data validation only to the entire row.
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
..thank you Leila for those helpful tips on Multiple Dependent Drop-down list. Happy holidays.
Thank you Hector. Happy Holidays to you too!
Hi,
It was very useful,
I have a problem as below;
An agent is filling a form under name of a customer few days ago. So, I need to excel show the report, if employee X process form Y on date Z, system show entire ROW report, else a blank row.
Great video. Helped me get through a project on google sheets. Thank you so much.
thank you for your video
can you explain why you use the offset function instead of indirect?
Amazing video!! Thank you for publishing it. It was the solution I needed for my management sheet
Question I am looking a way to create chart and I need the chart to use the color in the table cells basic thee color I am using green, yellow and red. How can I get this to work?
two years after the creation of this its still helpful. and i'm glad i spotted the comment about the dollar sign issue being fixed.i have 1000 cells o_o
is this also possible in google form?
I'm curious, is there a way to cause the second dropdown list to default to an option within the parameters of the first? In your example in A3 you have Game selected and, in B3, Five Labs; the issue I'm having is that, using your example, if I were to switch that A3 cell to Productivity it would show an error because Five Labs would still be the input in B3. Is there someway to cause the second dropdown list to automatically default to the first available valid option of the parameter set in the first drop down list, this being WenCal in your example. Or will you always have to manually go in and change something?
I really like your channel and love the way you explain things. I'm great at following directions, but not so great at figuring out formulas to begin with. I have a request. Maybe you've already done a video about it and I missed it? So, here's what I'd like to be able to do: a drop down menu to select a lesson objective. Once the lesson objective is selected, the cells beneath that objective will populate with the entire lesson plan. I can visualize the set up, I think, but don't know the right formulas to use.
What if i have to 3 tyre dependent list. I.e. main head, sub head and party name? Please help
Still can not be used for inputs into an infinite database. I can imagine bulding something like creating a specific sheet for the data preparation, transform the formulas to Array kind, and play with the ranges between sheets. Does it worth it?
Does an INDEX can be formulated as ARRAYFORMUA ??
How do i do this with a tabular data? and not report type data
Like it and its very simple thank you Leila
Thank you! 😊
is there a way to do a drop list in excel with a visible arrow inside the cell without those kinda ugly workarounds ??