Excel Dependent Drop Down List with Tabular Data without Named Ranges
Vložit
- čas přidán 7. 11. 2017
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Find out how to create a dependent drop down list in Excel based on a Tabular data set. Instead of having the contents of each drop-down beside each other as we had in the previous tutorial, we now have the contents of each drop-down right below one another. To distinguish which category belongs to which parent, the parent is listed right beside the category. This provides us with a table that has two columns. One for the parent and the other for the content.
⬇️ Download the Workbook: pages.xelplus.com/dependent-d...
We would like to come up with a dynamic formula so that when the user selects a category from the first drop-down (i.e. the parent list), Excel figures out which part of the tabular data-set to include in the 2nd drop-down. This means we need a dynamic formula that always shows the part of the table that has a match for the parent category.
Link to extracting unique items for drop-down: • Extract UNIQUE Items f...
Link to OFFSET video: • Excel OFFSET Function ...
Link to the other Dependent Drop-down List video: • Dependent Drop-Down Li...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/dependent-dropdown-tabular-file
To find out how to extract unique items for your drop-down from a list that can have multiple instances of the same item, make sure you watch this video: czcams.com/video/7fYlWeMQ6L8/video.html
Awesome, I was looking for this functionality in my excel sheets for many days. Dear you are amazing as your tutorials are easy to understand and very helpful. Please give steps to your tutorials so that anyone who wants to learn step by step excel find it more easy.
Our teacher (Leila Gharani) is one of the greatest teachers .
Love you .
Thank you, this is exactly what I was looking for. Took me a bit to get working since i was pulling from different sheets, but works like a charm now.
Madam,
Thank you! Thank you! Thank you so much.
I tried sreching this whole night from last 3days.. finally I got it from you..
Thanks. Keeping doing good job & God bless you!!
I'm happy that you found it here! Thanks for your kind feedback & your support!
You save my life for 1000th time, keep it up with the great work! Love your content!
Hi Leila. I love your videos. The viewers of this should be very grateful of videos like this. I still remember the night when I first used offset match countif within a validation list. Perhaps you have already covered this on another video but if you use offset as an array and add sum infront of it you get a dynamic sum, it's particularly useful for making a YTD model.
Thanks Andrew for your support & your comment. Very happy to hear you find the content useful. I have a similar video here: czcams.com/video/fkv3o4x_KHUo/video.htmln - I think you found it already :)
Thanks for the cool trick!
You're welcome mike!
Exactly what I'm searching for. Thank you very mush, you are my Saviour!! Great work there, will share with my friends.
I am addicted of your all Excel videos... big contribution LG thanks❤️
Thanks Dev for your support. Glad you like them : )
Exactly what I was looking for. Great video, your teaching is flawless! My job got done.
Cool, thanks!
Hello LG,
Beautiful videos all around.
Thank you!
Thanks a lot, it's amazing and so helpful. Brief and concise.
Hey. Thanks for all the great videos. I`m learning a lot, and seeing new ways to use familiar formulas.
I would love it if you could maybe start using the FORMULATEXT function while you`re doing these as well. Right now I have to wait for you to highlight a cell again to see the whole formula :)
Thanks, i've been struggled for hours, and finally found this video, thank you so much
Glad I could help
Thank you... took me a few minutes to get it but you made my day!
You're welcome - it is a bit complicated....I'm glad it clicked :)
Life-saver to find this again. A whole lot of help it is, thanks.
amazing - this has really helped and you made it so easy to follow:)
Wow - absolutely fantastic. Fixed a real problem for a team of 50!
Glad to help David.
This is fantastic, great stuff. THANK YOU very much. you made my excel.
Thank you for this, I have learnt something new. You got yourself a like and new subscriber
Very informative. Good work, helping a lot for the Excel users/learners/addicts/. Waiting for some more. One more big, big salute to your feet.
Thank you very much Gopala for your kind works and your support.
Madam, you are great and expert in Excel. I appreciate/admire your skills/talent in teaching Excel. Once again, sincere thanks from the bottom of my heart, with a fervent appeal to post more number of Excel tricks/tips/time saving techniques. Waiting everyday for your videos.
This is too good. Really helpful
I like it the way you teach very complex formulas in a very basic way. Well done and Thanks!
Very happy to hear that Saman. You're very welcome and thanks for your support.
Do you have any tutorial on making dynamic graphics and lines? which can control the length, height, angle etc.
I cover dynamic graphs in detail in my online visualization course - on CZcams I have a few as well. I'll make sure to upload more... Excel charts are one of my favorite topics.
Great clear tutorial for what is a great spreadsheet design element. Might want to include a brief explanation on how to get around the Data Validation formula bar not recognizing Table and column names as in the comments below from German Vargas, Carla V and Rene Oldenhoff.
Just Discovered your sight on Excel. I usually follow Mike's. very interesting and I will start following yours too from now on :)
Thank you John. Glad to hear that. Mike is great. I love his videos!
Awesome, thanks, Leila.
Woohoo!!! Sorted out my problem!!!!!!! Thank you so much!!!
You are very welcome.
very informative tutorial.... thanks Leila...
You're welcome Ronnie.
This is awesome, Thank you Leila!
You're very welcome Chaminda.
Firstly, thanks a ton for the wonderful educational content that you are providing.
This doesn't seem to be working with the latest Excel version, kindly advice if there is a different process to achieve this. Thank you Leila
Awesome this was a huge help!!!! Thank you! 5:59 for formula
You're welcome!
Thanks so helpful video 😊
Thanks - very helpful
Very helpfull video. Thank you so much.
You're very welcome Gue.
Dear mam,
Hope you're good,
Because you share this good things to all who all need like me..
Myself I didn't know about how work on excel I'm zero on it.
Alhamdhulillah by the grace of Allah...
You had came through CZcams
I have no words to describe my feelings and share it....
Because my work description mostly depends on Excel spreadsheet and makes reports for week and month so they want all progress....
I think I saw your all videos your way of teaching is fabulous and your English also very clear to understand..
For your video's only on CZcams without skip ads..
I'm not praise you...
Thanks for everything
Keep sharing
May Allah give you reward for your wonderful hospitality to us
Thanks again...
Many thanks for the kind words. I am happy if the tutorials are helpful to you.
Very useful for me,,,, Thank you so muchhhhhh
this one is easy to be learnt.
Thank you so much because of your awesome videos..I have a question,what should we do if divisions were not sorted exactly after each other??
Just what I needed to make work. Newer version of Excel doesn't return the #VALUE for mulitple cells and does a fill down like the FILTER function. This is great.
works perfectly well so far. The only thing i wonder is how to implement if division names aren't consecutive?
Then it won't work. I have tried it.
Leila please make video of this for every row. Love you
hi leila, really am thankful that I found your videos, i love your videos! this is a great help for me. You are an expert! I have a question, I am trying to create a CRM in excel sheet (old school), in relation to this video, what if the divisions are not grouped? as I am thinking of adding new contacts at the bottom of my contact list every now and then. I followed your steps in this video, but when it's not grouped, the dependent dropdown list is not the supposed list. Really appreciate your help.
Thanks for the lecture, great. What if you have a four column list or more and not the two like in the video?
Thank you ❤
Excellent👍👍👍
Great tutorial...
Thanks Kamayo. Glad you like it.
Perfect solution thank-you. I have another scenario very similar to this.
Two columns of source data (on a different worksheet): Products and Processes.
The aim is to have data validation for 100 rows of Column A for Products (in a dashboard worksheet) and Process data validation based on the Product, in Column B. Let's say that there are 3 products and 5 processes per product (all different).
How might I approach that successfully?
Got the idea in this video. Very nicely explained, but what to do if I need to have the dependent dropdown list with unique values.
Excellent ....pls make a video on bank reconciliation with cash book account automatically ... I will be obliged to you.
i promis i just today have asked Mr. Mike to give me way to understand offset function because it's really my nightmare but i'm thanks full to you i think i understand it very well in this video thank you so much really appreciate love you
I'm very happy to have helped Ismail! Many thanks for your comments and your support. Always appreciate them.
Leila Gharani my pleasure
Awesome Video Dear.
Thanks.
Good idea. I'm waiting for the work book
Workbook has been added : )
Thank you
Thank you so much for this video it helps me a lot to creat my inventory system. I have one more question how can we make this formula work in more than one sheet.
YOU ARE REALLY A PRINCESS , I LOVED YOUR STYLE , EXPLANATION IS GREATTTTT, YOU ARE SO TALENTED
PLEASE KEEP HELPING ALL OF US WITH SUCH FABULOUS VIDEOS
Thank you so much for the tutorials. Can I do this automatically in town, city, and country?
interesting trick Leila Thanks
You're welcome Douglas.
No other excel website had this formula in it, Leila Gharani is among the best Excel teachers in the world.
Many thanks for the kind feedback Darius!
@@LeilaGharani thats real talk, Leila
very useful Video
Thank you!
Thank you very much.for your super idea.pls how to solve this problem next sheet
what would you use in place of COUNTIF if the data is is horizontal and not vertical
Hey Leila, that's incredible!! You explain in such an easy way!! I was looking for this and i finally found it. However, this is not my final solution. I need to find a result using 2 conditions. F.E: there is a dropdown list with the value breakfast, lunch and dinner and another one with the values first, seconds and deserts. In the third dropdown i would need de values that match with this two condition. I've been trying to do it using the forumulas in the video but it doesn't work. Can you help me or, at least, give some clues? That'd be great and so useful for me!!Thank you
Hi Leila, thank you so much for your free content. Is really helpful! Wondering if you can point me in the right direction. I'm using google sheets, trying to replicate this excel tutorial. @ approximately 6mins into this video, you use data validation to make the drop down list. In google sheets I can't seem to get the same results with Data validation when I enter the offset function. Instead the output is the formula, rather than the desired drop down list. Is there any content you have that does this in google sheets? Repeating message from pinned comment of Leila's in hope someone sees this
Hi, love these tutorials, as it gives a good overview of functions I don;t normally use. I have to question though, when would you use this drop down? I'm trying to think of where it would be functional but cannot think of anything? Could this be used as a replacement for slicer which are quite limited in function?
Hi Erik - yes - just like slicers, it's another way to add interactivity to reports - so in this case instead of having slicers for all the apps, you would select from a list - and to make sure your list is not so long, you can restrict it by selection division first.
thanks
Could you please make a video showing, choosing from drop down list, Country then State, then District, then city. Selecting country should list only states (provinces) in that country only, like that, selecting province should show districts in that province only. Thanks very much for your videos.
Awesome. Thanks a lot, ma'am _/\_
How do we make E5 go blank first whenever D5 changes value? I have been learning a lot through your videos. Kudos!
I also need to know this, bar just deleting the value, the issue could be that people assume it’s picked up that value automatically if they aren’t used to using my form.
thanks thanks
I loved your video and explaination. Using to enter a sheet of detail expenses, with 2 columns (first looks up Named list (Category), second does lookup based on Category a subset of the Subcategory list (this videos example). Issue I'm having is that when I data sort lines by Category and Sub-Category (so I can create Subtotals by Category) the Data Validation on Sub-Category is all messed up. So line 12 Data Validate maybe based on Line 32 where it came from. Any suggestions?
Hi Leila, Just found this video which is great, thankyou. it does exactly what I want it to, when I click in the formula bar and then press F9, it shows the correct data that I want in the 2nd drop down list, but when I try to copy & paste the formula into the Data Validation Source box, excel says there is an error with the formula? I am using data from a Table in a different sheet, could this be the problem? Thanks in advance. Roger
Hey Leila.
I'm working with a data set that matches cars with their appropriate bulb types. The cars are sorted by year, make, model and qualifier. The drop-down is set up in the qualifier field, but it returns results for every occurrence of the model name throughout the list, and not just the specific year that the user selected. Is there a way to modify this formula so that it only returns values for the specific year the user selects?
Anxiously awaiting your reply.
Cheers,
Alex
Hi @leilagharani . theres any video of dependent lists based on id columns instead of header columns? Meaning I got A columns with the id and b column (categoryid and category name) with the display text, and another table with the id and let's say productid and product name. that could be helpful. Thanks in advanced
Hi, thanks for your tutorial. I am trying to use this formula to extra names of people who belong to a team. I have 5 different teams and about 160 people divided in them randomly. What I want is to have a my cells displaying team members depending on the drop down menu containing team names.
I used organized teams by organizing them alphabetically. In your example games get into a drop down menu. How can I get them listed in each cell one by one?
Thanks
Hi, I would like to make this expandable. Can you please advise me on how to do that?
Hi Leila! Thank you so much for your wonderful videos.
I'm trying to do this for a table with three nested columns (States, Counties, Schools), but I want to reproduce it for a Table that comes from a Data Query (since I might have to change the country from time to time). I do the same as you did, and when I try the formula outside of the Data Validation I get indeed the array of values (by pressing F9), but when I copy that formula into the list source within the Data Validation, I get an error (it doesn't recognize this as a formula). My guess is that the program fails to recognize the mention to the Table and takes the name of the table and column name as text, rather than a place to look into, but I don't know a work-around. Any idea of how to do this?
Thanks!
Having the same issue
Also having the same issue!
I was having the same issue and found the solution. Once I copied the formula into the data validation screen I just went in and manually entered the the tab name and the cell ranges for the table column that I was referencing. Works like a charm after that.
Love your videos. I have learned so much from you. Thank you! I'm getting a #SPILL error. Would love to know what I've done wrong.
The spill error means the result of the formula is spilling beyond the original cell into other cells. Some of the cells are already occupied by something else. You have to leave these cells empty so the formula can use it to give the results.
Thank you for sharing such informative video, i am seeking some effective method to represent large number of rows and columns (large data) in a single chart,can u suggest any idea.
You're welcome Fahad. Too many categories in a chart can be confusing for the reader. Is it possible to group some categories?
Thanks for sharing Leila, I wonder this won't work having data on a separate tab sheet right?
You're welcome. This should also work with data on a separate tab sheet...
Leila, THANKS. Amazing!
But How to solve when we have 3 columns and in the second one we can have a Dependent Drop Down List with the same values (tat will be detailed in the 3rd) and we want to remove the repeated ones? Thanks.
This is also my problem. Please help!!
1000 thumbs Up
Dear Leila,
Is that possible with the tutorial, my Data will be "Book1" file, while my drop-drop list will be at "Book2" file(meaning different file).
When i type the formula Data Validation, it shows " You may not use references to other worksbook for data validation criteria".
Hi Leila, Thanks for the video. How can i extend the validation to other rows for eg to E6,E7 etc
Did you find the solution?
Hi. Thanks. Is there a way to have a blank cell when you change to a new division? I noticed the last selection remains
Thanks for the video.. it really helped me .. but doesn't work when I upload this Excel in Google sheets
works perfectly when the column A has consecutive values. The only thing I wonder is how to implement if division names aren't consecutive?
Dear Leila Gharani, Thank you for tutorial.
I've one question.
How can I add corresponding select app for all cells on E column?
Hi Armine - do you mean you'd like to have each app in a separate cell in the E column instead of inside the drop-down?
Very good educative specially the offset and choose function. How can we show the unique List of values which is dependent on first Value? Please guide.
You mean the unique list for the division here? I would probably extract the unique list to a separate preparation table using the aggregate function and then with the help of offset get it's dynamic height. I'll add this to my list of videos....
Thank you very much.
Any ideas on how could this work if our database was in a different sheet?
Hi Leila, what if in the second column has non unique item?
Nyc vedio
Thanks Leila. I see this is an aged video so not sure if I can comment to get help. I have a similar list but my requirement is, I need to be able to see the entire list. Ex.: If I select " Productivity", I want to see all Apps mapped under it. So and so forth for Game and Utility. What's the way?
Hi Leila, please could tell you me- 06:17 when you change the searches on the first dropdown column, the filtered results from the previous search do not disappear on the next column. How can you make them go blank as soon as you select the next search on the dropdown on the first column? Please reply. Thanks.
Hi.. thank you for your video.. really helpful, but i have question, what if my database from the division is random, not grouping?
then you need to watch this video: czcams.com/video/7fYlWeMQ6L8/video.html
I owe you 👌👌👌
Happy to help.
Hi Leila! I love your videos and I have been learning a lot from them. I have a question. I have been trying to use the UNIQUE($A$4:$A$43,FALSE) formula to feed the Data Validation list but with no luck. I even created a Name with the formula but the Data Validation keeps giving the error "The Source currently evaluates to an error. Do you want to continue?"
What puzzles me is that I can use an INDEX() formula to feed the Data Validation list, but I cannot use UNIQUE() to accomplish the same. Is there a way to use the UNIQUE() formula to feed the Data Validation list? I appreciate your help.
Dynamic array functions can't (yet) be used directly or indirectly in the name manager - you have to put them in a cell and reference the cell with the hash sign. Check out this video for more info: czcams.com/video/waqzwMCYD9I/video.html
Can you do this for more then one column?
Hey, how would you go about if new divisions were added and taken out? For example, it could be a top ten list that changes every week (different divisions coming in and out). Can you automate the dropdown list to adjust to those changes?
You could probably use a data preparation table in between. Depending on the logic of which are the top 10 you want to show... the data validation would then refer to the data prep. table instead directly to the raw data table.
Thanks leila very good but what will be the solution if data in divison is not sorted or grouped? is there any formulas solution or we have to do this with vba? This will be very very fun
if it's not grouped, I would use a data preparation table in between. So when the user selects the first drop-down, I would first bring out all the occurrences of the app for the parent in a separate table, with the help of Index & Aggregate and refer the second drop-down to this data prep table results. I can add this to my video list if you'd like.
Yes it will be very kind of you if you add it.
Leila Gharani can i know wich video to handle that problem? Many thanks
The video is not on CZcams yet - I hope to have it up in January.
Leila Gharani thanks leilouna i am looking forward
Is it possible to make list depend upon the list as it occurs in drop-down list depending upon drop-down list?