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.
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!
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.
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.
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!!!
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
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 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!
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.
@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
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.
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.
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" ?
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
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
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!!! 😅
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!
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
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?
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!
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.
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?
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'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???
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?
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?
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.
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.
Hey, how were you able to do it for so many rows? When I try to do that, it only shows me the options for the very first dependent. Hope that makes sense!
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?
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!
I like your video here but I don't like it. Really, I'm just so glad that you've explained so well. How Google has the invisible dollar signs and the way to get around it. Thank you
ΤΗΑΝΚ you for this! I was lucky to read the comments as I need it for multiple rows. My only PROBLEM now is that adding a row ruins the whole sheet. Any solutions?
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
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.
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?
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?
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.
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?
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!
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.
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.
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 🤩
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.
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
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.
Leila, I like the way u speak out your English .. It's, favourable for us all. Even here in Africa
A huge thank you for these guides! They are really easy to follow!
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!
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!!
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!
A channel I suggest often as an example to be a best youtuber.
Wow, thanks
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
This is awesome! Thanks for sharing this tutorial on drop-down lists :)
My pleasure :)
If you go into the second data validation, you can actually remove the $ and simply copy and paste the validation. Thanks for the tutorial!
@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
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!
You are an absolute life saver - I cannot thank you enough for this!
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.
Great Video. How would you do this if your data is in tabular format?
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!
Hi leila
after weeks of watch serial UA-cam video on how to go about this, am soo happy it finally worked....
thank u
You are welcome 😊
Biggest Thanks Mam..... My long time issues now solved ur video watching after Lot of thanks
Most welcome 😊
Fantastic video! I'm interested in Appscripts, do you have videos or courses on that. Congratulations & thank you
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
extremely helpful video
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.
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.
Thanks the video!
6:42 Should I change every B5:B14 for myself, not automatically?
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" ?
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
Thank you again...really appreciate your videos
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 solves my current problem, thank you so much!!!
Glad it helped!
Thanks Leila, you are awesome!
You are master in making simple things difficult.This thing is easliy can be done with name range and indirect function in google sheet
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!!! 😅
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!
Very very Helpful Thank You!
Good Leila, i was searching this function only
useful
This saved my day! Thanks a lot :)
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
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?
Thank you so much! Im used to excel so needed some help to get this task done, it worked perfectly =)
I learned and progressed a lot by watching your videos. Thank you very much madam
It's my pleasure.
Thank you so much!
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!
its really helpful at the start, but how if the row is so many? do you have any solution on this?
Very Helpful... Really appreciated 🎉😍😘
thanks so much Ma
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.
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 for sharing such a useful trick. 👍
Ma'am, I have a query, 👉 How to change/move specific cell value on the base of drop-down list.
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.
Its a fantastic Solution !
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
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!
This is great..I used unique + filter instead of index..
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?
Great video. Helped me get through a project on google sheets. Thank you so much.
Like it and its very simple thank you Leila
Thank you! 😊
Amazing video!! Thank you for publishing it. It was the solution I needed for my management sheet
So helpful! Thank you
..thank you Leila for those helpful tips on Multiple Dependent Drop-down list. Happy holidays.
Thank you Hector. Happy Holidays to you too!
you saved my life so much thanks!
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?
GOOD ONE!
I'm about to a dropdown lists tutorial. We're thinking alike. 🤗💡
Thanks Oz! Looking forward to seeing your take on it.
thank you for your video
can you explain why you use the offset function instead of indirect?
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.
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.
Great, Thank you! I used your tip for more than 1000 rows :) Lovely!!!
Hey, how were you able to do it for so many rows? When I try to do that, it only shows me the options for the very first dependent. Hope that makes sense!
@@jennyelomaa1685 you need to use =TRANSPOSE and create a lot of rows, so you will be able to have multiple drop downs ;)
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
Thanks a lot. Great tutorial! 100% effective
For drag validation go to first row remove $ sign from value then copy it and paste through data validation only to the entire row.
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?
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!
Very useful video thanks
Cool Leila. Nice and clear.
Thank you! Cheers!
I like your video here but I don't like it. Really, I'm just so glad that you've explained so well. How Google has the invisible dollar signs and the way to get around it. Thank you
ΤΗΑΝΚ you for this! I was lucky to read the comments as I need it for multiple rows. My only PROBLEM now is that adding a row ruins the whole sheet. Any solutions?
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
You are my angel!! Thank you so much!!!This is awesome!
You are so welcome.
this was super super helpful for me! Love your videos
lol I just commented on your other video asking about this video. Good stuff.
Hi Leila,
I have 1500 rows! while applying the index formula & transpose how shall i do it for all rows now?
Wow, extremely useful, thanks Leila!
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.
Can i do the same thing to import it to google form ? Or you have another way? Thanks
OMG, thank you very much, you did save me today!!!!
I love uuuu.. It solves my problem.. I can start my sleepung beauty now
I would like this but on a table. To have different row with Productivity, Games, etc, Is it possible?
I see Leila - I press Like.
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.
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?
Happy Thursday your highness
These videos helped me so much
This and last week
Thanks
Haha, it's Tuesday. I'm glad you enjoyed the videos 😊
@@LeilaGharani 😂😂🤭😚😚
@@LeilaGharani sorry
I was enjoying lambda yesterday 🙏
very Impressive
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?
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