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!
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.
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.
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?
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
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?
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, 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.
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! 😊
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, great video, I've done something similar using the filter command. I do want to ask though, is there a way to get the drop-down list items from A-Z?
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((
Every time I do the second column it removes everything in the first column, then when I go back and fix the first column it deletes everything in the second column. ): What am I doing wrong?
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 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.
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?
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
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???
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.
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
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?
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!!! 😅
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" ?
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!
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.
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?
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 Leila, I am Not able to the Remove $ Sign in Reference in the Data Validation as per the Respective Cells in 2023 Kindly Suggest the Solution Thanks Waiting for the Response
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?
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
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?
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?
Having to do data preparation just makes this whole thing not scalable. Good God, great tutorial tho, not really your fault, just Google Sheets being very limited
Someone help me coz i still can't seem to copy paste and i want the drop down to cover 1000 ... i am using an outdated version of chrome or what might be the issue because i still can't see no dollar signs
According to this Data preparation column and First cell is working properly. But I applied paste special format also. But it is not working properly. As a help if you could send me an email I can add you as a commenter to my google sheet
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.
