Solution for the space. Keep all the regular typed names with spaces like normal. Use the underscore for the named ranges. In the final formula in C6, use the substitute() function to replace the spaces with an underscore. It makes for a longer final formula, but the aesthetic is more human. =INDIRECT(SUBSTITUTE(B6," ","_")) Great video! Now if only you could use INDIRECT() in the data validation like you can in Excel.
As of Feb 2023, there are some extra steps needed in the Data Validation section (At least on my instance of Google Sheets). In my case I had to click on ADD RULE then select DROPDOWN (From a Range) in the Criteria section. Other than that, it worked well. Thanks!
Okay, but let's say you want 1000 rows to all have the same 2 drop-down options, but that correlate with the corresponding row, without needing to create new "indirect" tables for all 1000 rows. There has to be a way where instead of selecting the dropdown menu cell directly, it will select the dropdown option you chose in the corresponding row. there has to be. But everything I have tried on google/youtube has come back as an error. Can anyone help with this?
Same for me. I would love to change my accouting sheet from Excel to Google sheet. But with over 2000 rows this indirect will not work. in Excel I use indirect(A?) and it works
Best explanation I've watched. Most stop after showing how to set up the dependent drop-downs on one line and don't show you how to do that on multiple lines. Thank you.
I have seen the presenter in a box in the bottom right corner before but I have never seen the cut out silhouette of the presenter! Excellent productions value and video resolution quality - not to mention the great content. Thank you!.
This is a great tutorial - it explained the process clearly and gave enough context (but not TOO much) to ensure full understanding. UNFORTUNATELY the Google Sheets feature itself is somewhat restricted in its abilities: it doesn't work if the column titles contain spaces OR if they begin with numbers. I see that with more jiggery-pokery with formulae I could use the substitute function to handle this, but it would end up being stupidly convoluted. [Sigh]
You can also use the SUBSTITUTE() function to change the space to an underscore when looking at a name in the list so it still works this way. Example based on his example: =INDIRECT(SUBSTITUTE(B4," ","_")) So if someone selected "Lady Finger" it would convert it to "Lady_Finger" and then pull that named range.
Thank you, Thank you, Thank you... I've been trying to do this for a while now with LINKS but it's just not possible. I had so many pages but I learned a new FUNCTION today.. My application is a bit different as I am replacing a grid of data rather than a list, but it works just the same. Now I have only ONE PAGE... I knew there had to be an easier way. Subscribing for sure!
What if the "Fruit" column is infinite - It's not going to be just 3 rows, it's going to be hundreds of rows, I don't think the Indirect function is suitable function as you need to create the indirect hundreds of times. Is there any other way to go about this?
Say your list of fruit starts at D5 and goes to D50 but you need to make sure you can add infinite items to your list in column D, then you would do D5:D - This will infinitely select column D starting on row 5. You can also make it say like D5:F and it will select all cells from column D to F, from row 5 down infinitely. Say it starts at D5 and goes to Z5 but again you need to make sure you can keep adding items without having to update formulas each time then you should do D5:5 - Again you can have items on multiple rows, say every row from D5 down to D7 all the way over as far as you want/need has info in it- then you can do D5:7 which would select everything from row 5 to row 7 starting at column D infinitely
@@GusTheAnt That didn't answer Husna's question, which is the same as mine. I have literally hundreds of "fruit" rows that need the dependent drop down list. Column A: Initiative (manually added) Column B: Big Priority category (eg Fruit, this is the big bucket of priorities) Column C: (depending on what is selected in Column B) list of departments that work on those Big Priorities (eg fruit varietals) Therefore, there can be infinite additions in Column A which need to select one item from the drop down menu in Column B and then 1 item from the drop down in Column C which is dependent on Column B Besides doing it manually, I can't figure out a way to add hundreds of rows with the multiple drop down menus that are dependent. Super frustrating
Just doens't work for high volume of lists. I'm trying to find out how to make this for thousands of items, but without a native function inside the data validation I think it's just impossible.
@@anmerpozzobon9083 Indeed, it just doesn't seem possible in GSheets. In Excel it seems to work just fine, as it "adapts" the formula when you extend the rows. I've read about scripts that help doing it, but haven't been successful in implementing any. If anyone has a solution for Dependent Drop Down Lists in GSheets, please, mark me on the comment / video :)
Great Video. However, for each row, do we need to create separate column of data with indirect? It can be easily manageable in Excel in this case. The Named range automatically, takes the next row as reference and updates the data validation data. Usually, with offset and match formulas in sorting order. If I have 1000 rows, then it is quite a big task. Any alternatives for this with formula instead of scripting?
it's too bad we can't use the indirect function in the data validation criteria directly, like you can with Excel. That allows you to have multiple rows of the same dependent dropdowns, without needing to put an intermediate list somewhere else. This alone is a showstopper for me.
@@carlysorensen2198 have you managed to solve this ? after some digging i found out you can do it by adding a google script. hope it helps (watch?v=lIjrevuWMB8)
I just spent hours creating unique data validation criteria for 50 rows, which of course required 50 unique indirect functions on a separate sheet. My head is pounding!
So I figured it put, if someone hasn't already. You haven't to rearrange your list to go horizontal. You can do that with copy then paste TRANSPOSED. Then reconfigure your named ranges for the new cell ranges. Where your filtered results go you do have to drag the indirect formula down make sure with no $ signs for every rows you want. Then go back to your data validation drop down list remove the $ infront of the row numbers but not columns and you are able to drag it down and will give you independent lists for every row. Hope that makes sense.. it worked for me
Thanks for the video. is there any way to copy the multiple dropdown lists with another command point because I have multiple cells and don't want to write an indirect formula for each one?
Great video. I would recommend an update on it, much has changed in 2 years. It has pointed me in the right direction but i had to to much more "exploring" to get it to work.
Great tutorial. I'm having a problem where this only works on the first row of my drop down, even though the data range is extended the the last row. Any ideas on how to fix this?
Yeah~ it works! I used the template for quite a while for accounting and always wanted to improve and customized some of the functions but didn't know how. Thanks for sharing!
Hi, thanks so much for the video! Just a question - if I use this method for tracking clients I work with, some of whom may have multiple funding sources, let's call them Fund A, Fund B, etc. but not all do, then would have available hours of funding within those categories, could I use this process for that workflow?
Hi there, thanks for your video. I realize my question is not directly related to your video but I was wondering if Google sheets has the capability to create a simple drop-down menu with items that are removed from the list when selected? Thanks for your reply in advance!
The last part where you'd need to manually create data validation for each dependent cell does not seem right. I tried the rest of it and it works and is very helpful. But I have 400 rows and I am going to keep adding more rows to my sheet. There must be another way to do data validation for the whole column. If anyone knows the answer, could you please let me know. Other than that, great tutorial!
Greetings! Thank you for all your amazing symposiums. My question is can you create a Dropbox for typing in the form of to be embedded? I'm trying to be able to type on doc pdf . I need an to embed on the application. Thx in advance
Couple of points: 1. Named ranges aren't case sensitive, so you can write "bananas" and it will still work. 2) It's not a good idea to use multiple named ranges for the amounts remaining - instead this should use vlookup or similar. That way you won't need to use an underscore in the names for the fruit ("Lady_Finger") which is required for named ranges, but not for vlookup.
Hi if I want to create a whole column of drop downs do I need to repeat the process for every single cell/ drop down or is there a bulk way of doing this.
Hi, great video,I'm a window cleaner and was wondering if instead of fruit I had addresses in column A and in column B I had payment methods Ie: cash,cheque, card, Bank Transfer but it was all mixed up, and in column C is price, is there a way of extracting all the cash and adding it up then the same with card and so on. Hopefully this makes sense
What if I have a very long list of different things? for exqmple, I'm doing expenses and just want a simple dependency table for categories and sub categories. Do I have to go through every single row and do this?
What if the data you are using for the drop down options are not in consecutive cells? As in, what if the cells are split by data that can't be moved. So if wanted the range to be F6 (apples), H6 (Oranges), J6 (Pears). is there a way to make sure the data in G6 (Bananas), I6(Lemons) don't show up as an option in the drop down?
Hi , will be grateful for help If i have bananas and their types below, and than oranges and their types right below, and so on (when all the ranges are in one column), how could I create a drop-down list? Is it possible to choose a separate cell for that drop-down? Many thanks 💚
Great videos, could you share a case in which we have multiple dependant validation such as location validation (province, perfecture, and districts are depending on each other). I made one with MS Office, and when I open it on google drive, they just read my match formula results instead of showing the list. I'm putting together indirect and concatenate function to call my name ranges. Please help
Please help this teacher! I create my lesson plans on an excel sheet. I would like to use your advice for drop down features and formula to insert the lesson standards for each activity I teach. I think I can follow your steps in this video to create a list of standard codes, However because there are so many and I don't have them memorized I am looking for a hack. .. so bear with me. Is there a way to view a whole sentence (each standard) in the drop down, but only have the standard code appear in the lesson plan document when chosen from the drop down list? For example, if the standard in the drop down reads "I.A.a.1. 1. Engages in physical activities with increasing balance, coordination, endurance and intensity", when selected from the list I would only like only "I.A.a.1.1" to show on the actual plan. So I want both options available: to view the whole sentence But only insert the code to the lesson plan. I cover between 1-6 standards per activity so i don't have the space to write each one in a whole sentence in that cell. I hope I asked my question clearly enough.. and more than ever I hope you have a solution that could help me! Thank you!
Hi- I am a school counselor and am trying to connect information from a master sheet (contains all of my caseload as well as information pertaining to my students) and I need to get that information to individual sheets for each student so it is personalized for them and only contains their information. I will have over 400 students. Is there a way to do this in an efficient way?
This seems to be fine for simple things, i don't think Sheets handles the named range very well (you can't search for what you created in the named range window). The underscore between words looks bad and is cumbersome to type. It did get me started and atleast i found the problems early, but i wouldn't think this more than an example and a direction to something that can handle hundreds of entries (easily) would have most desired.
hello, need your help to create a dependent dropdown in *google form* using data or dependent dropdown menus from a google sheet. please guide me or if possible make a video on it. P.S : Don't want to use logic based section shuffle or *form ranger* or *cascade formatting*. thanks in advance
I tried to do this, but in my first drop-down EG (apples) I have 5-15mm door bottom gap, I have managed to make the indirect function work if I shorten the the name to (Bug) however that's not going to work out when i have hundreds of different itemised faults that require a selection of rectifications
How do we get rid of the "invalid" warning when we go back and select a new value from the first drop-down? This seems to be intentional with Google Sheets, i.e. not an error, but it quite distracting to user of a sheet with multiple drop down lists. Any idea how to get rid of it?
What is I need to build a multiple list which can scale ofer say 100-200 odd entries? Is there a simpler way where I don't have to create endless named lists linked indirect columns?
I am having a problem with Named Ranges. I put the name as the name of the top cell of the range. I then put in the Sheet Name! with the range necessary, but I do make it longer to accommodate for more data. I hit done and it says "The specified range is not valid." Its copied and pasted from the Sheet. Help!
When I add a Named Range, you can only use 1 word so if I am trying to create a list of items that have multiple words for the name, I cannot create a named range that matches so this doesn't work. Any other ideas?
And this is why Excel is so much more advance as you can just do an indirect for each row within the Data Validation Drop down so you don't have to create multiple lists everywhere which is not ideal if you have 100's of rows of drop downs. PLEASE can someone tell me there is a better way to do this?
