Realistic Case: Dependent Drop-Down Lists in Excel with Multiple Words & Spaces in 1st List

Share
Embed
  • Published on Nov 1, 2017
  • Download the Workbook here: www.xelplus.com/excel-dependent-drop-down-lists-multiple-words-spaces/
    This video builds on the methods shown in a previous video: thexvid.com/video/CIJbcBBH88M/video.html which showed how to create an Excel dynamic drop-down data validation list that was dependent on the selection of another drop-down.
    In that video the first list contained numbers whereas in this video we take a look at a more realistic case where the first list contains text with multiple words and spaces. This approach works if your drop downs are in different worksheets as well.
    Check out this video to create drop-down lists on tabular data sets: thexvid.com/video/avVLznHODVA/video.html
    Index Match basics explained: thexvid.com/video/F264FpBDX28/video.html

    ★ My Online Excel Courses ► courses.xelplus.com/
    ✉ Subscribe & get my TOP 10 Excel formulas e-book for free
    www.xelplus.com/free-ebook/
    EXCEL RESOURCES I Recommend: www.xelplus.com/resources/
    Get Office 365: microsoft.msafflnk.net/15OEg
    Microsoft Surface: microsoft.msafflnk.net/c/1327040/451518/7593
    GEAR
    Camera: amzn.to/2FLiFho
    Screen recorder: techsmith.pxf.io/c/1252781/347799/5161
    Microphone: amzn.to/2DVKstA
    Lights: amzn.to/2eJKg1U
    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!
    #MsExcel

Comments • 184

  • Najlaa Al Bukhari
    Najlaa Al Bukhari 3 days ago

    Thanks soooooooo much it is really helpful 🙏

  • Easy Tech MM
    Easy Tech MM Month ago

    Awsome, it worked like magic

  • Spyridon Stavrou
    Spyridon Stavrou 2 months ago

    Leila you are top notch. You have done a fabulus job

  • Hari Krishna
    Hari Krishna 2 months ago

    Hello Leila, is there any way to create a table that changes based on drop down menu selections. The value of previous table should not be there when I change drop down selections. Thanks

    • Leila Gharani
      Leila Gharani  2 months ago

      Do you mean like a reset? If so I have a video on that: thexvid.com/video/wWasYHG1lmM/video.html

  • Md.Saiful Islam Tuku
    Md.Saiful Islam Tuku 2 months ago

    Sir,
    Place make video missing serial number highlight this cell. Not another sheet,row&columns .
    thanks

  • Kai Geertsma
    Kai Geertsma 3 months ago

    Hello Leila,
    Is there a way to create an "All" option in top of the drop down list. That you select one cell that automatically selects every cell in the drop down list?

  • Dinesh Ravi
    Dinesh Ravi 3 months ago

    Thank you great

  • Yogesh Khanna
    Yogesh Khanna 3 months ago

    You are awesome

  • Britney DeHond
    Britney DeHond 4 months ago

    Thank you so much! This was so helpful

    • Leila Gharani
      Leila Gharani  4 months ago

      You're most welcome Britney! Glad the tutorial is helpful.

  • Timothy Franjesevic
    Timothy Franjesevic 4 months ago +1

    How would one write this if the Match lookup value was in column "A" and we wanted to see the values in column "B" and "C" that correspond with that lookup value. Following your example if my selection list was "A5:A19" instead of "A4:C4" and I tried to return Jellyfish and Atmos when looking up "Mirrr" what would be the equation? I tried; =Index($B$5:$C$19,MATCH($G$4,$A$5:$A$19,0),) but this returns an error. I have tried modified versions of this and cannot seem to figure it out.

    • Gemma Rodelas
      Gemma Rodelas 3 months ago

      Same problem here. Please help us Ms. Leila. What we will do if the data needed are across columns not rows. Thanks!

  • Parth Gunjal
    Parth Gunjal 4 months ago

    Hi,
    Thank you for the great video.
    I am getting correct values for index whenever I am pressing F9 to see the list, but when I try to paste it in the data validation tab, it says that there is some error in the formula. I have following differences than what you showed:
    1. My lookup value is itself a result of another if statement.
    2. I am referencing the array from different sheet in the same workbook
    3. My arrays and lookup values are part of a table(I can change it if that is a problem)
    Kindly let me know your thoughts on what problem might be. TIA.

    • Parth Gunjal
      Parth Gunjal 4 months ago

      Hi,
      I just tried changing the table to normal text and it worked perfectly well. My motive to put the array in the table was to make it dynamic for future expansion, but this can work too. Thanks!

  • MUHAMMAD IQBAL Razaq
    MUHAMMAD IQBAL Razaq 5 months ago

    Thank you for the video! However my drop down list consist of data vary from 5 to 50 drop list, is there any way to delete the blank list from the drop down view? Just to make it look better and efficient, Thank you 😊

  • Slider9sowyn
    Slider9sowyn 5 months ago

    the online version of excel doesn't accept formulas... how to di it there

  • SnookRS
    SnookRS 5 months ago

    can not thank you enough Leila, this is the best excel description, great explanations, great timing as well!

  • nocomment8273
    nocomment8273 5 months ago

    I must be doing something wrong. When I use the match function and give it the lookup value (the drop-down cell) it always throws an error. What is it that I'm missing.

    • Leila Gharani
      Leila Gharani  5 months ago

      Try downloading the workbook - you'll find the link to the blog post in the description of the video. Scroll down and download. It's easier to cross-check then.

  • Victor Girbu
    Victor Girbu 6 months ago +1

    it's not working

  • Swapnil Akewar
    Swapnil Akewar 6 months ago

    Hi thank you for the helpful videos. Can i extract data from multiple drop down lists in excel. For Eg.
    Drop down 1 (A1), Drop Down 2 (A2) Drop Down 3 (A3) Drop Down (A4) . I want to extract data basis these drop downs. if any of these drop downs have any content, then I want list of details get extracted and fliterered basis on selection of these drop downs . just like any software. i can you help. is it possible

  • ITECLES
    ITECLES 6 months ago

    Clerio, MOzambique... Maputo. Thanks 4 all... its ExcelLent

  • ITECLES
    ITECLES 6 months ago

    Xcelent

  • Imran Mushtaq
    Imran Mushtaq 6 months ago

    Very informative as usual

  • Gary White
    Gary White 7 months ago

    Hi Leila, thanks again for a great video. What I I am struggling to solve is a series of dependent drop down lists (5) with repeated (7,600) entries. I can get your formula to work but I cant seem to avoid the duplicated entries in the drop down.. any thoughts? Many thanks ! - Gary

  • فاروق السامرائي

    I have a question:
    If the two drop-down lists and each one in a different worksheet, how can I link them?
    thank you.

  • Pablo Aguilar
    Pablo Aguilar 8 months ago

    THANK YOU!!! Is there any way for the dependable cell to become blank if the first cell is changed?

    • Leila Gharani
      Leila Gharani  8 months ago

      This video might help: thexvid.com/video/wWasYHG1lmM/video.html

  • فاروق السامرائي

    hi,
    I apologize for the previous comment, and I have worked with me successfully, but I still have a problem, namely: whether there is another drop-down cell under "Productivity Div" For example, how can the compatibility in their work?
    thank you.

    • sukrat kaushik
      sukrat kaushik 8 months ago

      @Leila Gharani thats too much complex.. any easy way ?

    • Leila Gharani
      Leila Gharani  8 months ago

      For multiple drop-downs please check out this detailed post from Debra: contexturesblog.com/archives/2018/06/28/multiple-dependent-drop-downs/

  • فاروق السامرائي

    I thank you very much for this useful illustration, you are really a genius, but I have a question: how can I add an equation so that it prevents repeating the evidence?
    Thank you.

  • Wayne Edmondson
    Wayne Edmondson 9 months ago +1

    Hi Leila.. more drop-down magic. Thanks for the video and Thumbs up!

  • Mohammed Firoz Alam
    Mohammed Firoz Alam 10 months ago

    a lot of thanks!!

  • Pakistani Dramas khan
    Pakistani Dramas khan 10 months ago

    I love you will you merry me

  • Imran Mushtaq
    Imran Mushtaq 10 months ago

    Step to step knowledge of MS Excel in your daily routine life whether profession or private.

  • Sarah Bailey
    Sarah Bailey 11 months ago

    how do you fix it in the 3rd list?

  • Brigh N Quisitive
    Brigh N Quisitive 11 months ago

    amazing as always, you never disappoint!

  • CIAO Yuko Leathers
    CIAO Yuko Leathers 11 months ago

    great job

  • Mostafa Hosny
    Mostafa Hosny 11 months ago

    Outstanding!! Very nice explanation and graphics! Pls continue in this direction .. Thanks forever!

  • Juan Garcia
    Juan Garcia Year ago

    Newbie question (maybe). Hoping someone can answer. How do I make this work if my list is in a separate worksheet (same workbook)?

    • Leila Gharani
      Leila Gharani  Year ago

      If you just have one list, check out this video: thexvid.com/video/FRiFfKb_B_A/video.html
      I do an example with the list on another sheet.

  • Megha Syam
    Megha Syam Year ago

    💖💖💖

  • Lloyd Christmas
    Lloyd Christmas Year ago

    Really cool. Thanks.

  • Mariya Dayan
    Mariya Dayan Year ago

    Excellent video! Thanks for your help. I'm trying to put my "reference list (the one you have in A4:C19) on a separate sheet in the workbook. Excel gives me an error. Is it possible to do this with the reference list in a difference worksheet?

    • Bilwinco Hardware packaging
      Bilwinco Hardware packaging 11 months ago

      If you didn't get an answer I found it furtther down thexvid.com/video/avVLznHODVA/video.html

  • double007tmb
    double007tmb Year ago

    Your videos are so helpful and thank you very much

    • Leila Gharani
      Leila Gharani  Year ago

      You're very welcome. Thank you for your support.

  • cucuzza21
    cucuzza21 Year ago +1

    AWESOME video (as well as the one linked in the video's description)! This fits perfectly with an application I'm trying. I do have a question, however. Given your example with 3 columns that don't have the same amount of items in each list, how do you get rid of the possible blank selections in the drop down menu? To see a visual example, this is what I'm talking about: (i.imgur.com/mXq6QGn.jpg). At 6:23, you say that a user can use Custom Formatting, but I think that's just in the case of how you have the data laid out in the video. Any tips on this? The thing is, I have about 80 lists of varying lengths and using the INDEX MATCH is more desirable over the CHOOSE function because there's a character limit in the "Source" box when choosing Data Validation > List. The CHOOSE function, however, allows you to pick exact ranges (thus ensuring no blank spots) vs defining an array in INDEX MATCH and having blank spots because you're choosing an array of lists that could have blank spots because not every list has the same amount of items.
    Regards from Joe, North NJ, USA

    • cucuzza21
      cucuzza21 Year ago

      That would be awesome! Can't wait to see it.

    • Leila Gharani
      Leila Gharani  Year ago

      Actually, I just made a video on this :) I'm aim to upload it next week Thursday. It shows you how to cut off the empty cells from your drop-down so they are different sizes depending on how many values you have in each list.

  • Sohaib
    Sohaib Year ago +1

    You are an angel !
    BIG Thank you from norway

  • Ottmar555
    Ottmar555 Year ago

    I found this really helpful.
    The only problem I see is that if the selection matrix contains empty cells, they carry over to the list, even if ignore blank is selected.

    • Ottmar555
      Ottmar555 Year ago

      I'm very thankful for your help and your quick response. You got yourself a new subscriber!

    • Leila Gharani
      Leila Gharani  Year ago

      Yes - and for that I'll be uploading a video soon - probably next week :) It'll show how to set it up so it's dynamic - includes new apps in the drop down - and also ignores the blank cells on the bottom.

  • raffi shirinian
    raffi shirinian Year ago

    im new to power pivot thanks to you Leila :)... i have a question, when i filter a column in the data model (to exclude some countries for example) it isnt reflected in the pivot table created from the data model even after refresh. if so, then why the column filters are there in the data model to begin with.... thanks a lot Leila.... also registered in your Udemi courses VBA and power pivot... great you have courses there

    • Leila Gharani
      Leila Gharani  Year ago

      You're welcome. I don't have a power pivot course though VBA yes, but no power pivot - is that Chris' course?

  • raffi shirinian
    raffi shirinian Year ago

    youre the best thank you

  • Syed Hassan
    Syed Hassan Year ago

    Thanks for the video. However I have slightly different query, where I have a dropdown list and it will retrieve all the values (column in the table) from a table based on that selection. How should i do it?

    • Syed Hassan
      Syed Hassan Year ago

      That will be great too. Your videos are so much exciting. In my particular case, I i have few columns (i.e Sales person, area, product, volume) where if i choose sales person from the dropdown It will show all the sales record for that particular sales person.

    • Leila Gharani
      Leila Gharani  Year ago

      Here you can use a similar technique, except you'll probably need to use name manager since you can't directly past in the data validation box. I'll actually be making a video on this in the next month or so....

  • arahman Abdelrahman
    arahman Abdelrahman Year ago +1

    Always: "thumb up"
    Talented people are always up there

    • tha2ir Talib
      tha2ir Talib Month ago

      beautiful mind , and always perfect .
      Keep on doing magic .

  • Chandan Kala
    Chandan Kala Year ago

    Thank you for the excellent video... The 2nd case has more practical usage then the first one (in my opinion)

  • Esan Ariam
    Esan Ariam Year ago

    Thank you Very much!

  • Phani Kumar
    Phani Kumar Year ago

    Dear Madam, It is very helpful. i have been trying to shift the drop down into row field but it is generating error like #value!. my data has hundreds of line items so i wanted to put the validation in rows..I look forward for your reply..

  • rosh 12345
    rosh 12345 Year ago

    Thanks for the video. It's really nice as usual
    I have two question: how to how many customers I have from table where customers names are repeated??
    Second question is: how to know how many families in certain city from table contains first name, last name, and city
    Please give me the answers
    It is important

    • Leila Gharani
      Leila Gharani  Year ago

      You're welcome Hevi. I have a few other videos on dependent drop down lists that work with duplicates and data in tabular format. Have a look here: thexvid.com/p/PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y

  • Norma Perilla
    Norma Perilla Year ago

    Can you some how "write down" only some characters from the selection of a drop down list (e.i abbreviations, codes, references)

    • Norma Perilla
      Norma Perilla Year ago

      It did not work combined with the drop down list. Thanks anyways

    • Norma Perilla
      Norma Perilla Year ago

      Let's say I am making a restaurante menu, and my drop down list menu not only has the info about the dish, buy also a code, or a reference number. When I click the arrow I can see the entire description, but once I select my option, only the code will show up on my cell (not the rest of the description). Thanks for your time Leila.

    • Leila Gharani
      Leila Gharani  Year ago

      Oh - yes - you could use the left function like =left(A1,3) - where A1 has your long text in it....

    • Norma Perilla
      Norma Perilla Year ago

      Thanks for your reply!! I mean, if I select the line "001 Fish and rice" , from the list but I only want the "001" showing as my final selection

    • Leila Gharani
      Leila Gharani  Year ago

      You mean to type it in and the drop-down finds a match? You can do that if you use ActiveX controls. If you want to use normal Excel functionality, you can set up a data preparation table and things can get a little too complex. Alternative is to use VBA.

  • konli wong
    konli wong Year ago

    if you have a data list of , lets say 500, it wont work
    its has to be a "searchable" drop down list

    • Leila Gharani
      Leila Gharani  Year ago

      Hi - yes there is a way without VBA - it's a bit complicated though because you need a few formulas and a data preparation table. I find the VBA approach quite easy to setup and to maintain - I'll add these to my video lists for TheXvid as well....

    • konli wong
      konli wong Year ago

      Wow a fast response !But I know that there a fast way , to do it , with out getting into "VBA"Just using excel formulaI don't know how it works, but I hope you can tell me how it worksThe way I was working around this, was that in Excel I had my data list about 3000 item.then connect it to access, then work on access to input the informationThe problem where I Work , people don't know access and don't want do learnand I end up capturing all the informationPeaceLeila

    • Leila Gharani
      Leila Gharani  Year ago

      Agree. Searchable lists would be the way to go (this one is covered inside my VBA course....)

  • Vishwa Jeet Dwivedi

    very nice video and done in very simple way, its very useful for me. thank Leila

  • Jaap Wieringa
    Jaap Wieringa Year ago

    Thx for your helpfull videos. Question, I'm looking for a solution where a user can select form a readable dropdownlist but when (s)he makes a choice I want to just have corresponding value (ID) in my colum. So fe ID A Automotive B Shoes C Jackets , in dropdown Automotive, Shoes and Jackets in rows that are filled A B or C.
    I found an option with VBA but hope it is also possible with INDEX MATCH and/or OFFSET?

    • Jaap Wieringa
      Jaap Wieringa Year ago

      thx for your answer but I think you didn't totally understand what I meant with my question. In a data entry form I would like user to be able to select items they can understand/search for in a dropdown but when they made a choice I just want to have an ID in my sheet because in analyzing I can do with ID's and there's no room for a wide enough colom for full descriptions.
      So data validation won't work I suppose but I got hope since I saw what you can do with, index, match and offset.

    • Leila Gharani
      Leila Gharani  Year ago

      Yes - you should be able to use a simple Index Match for that or even Vlookup. Have you checked out this video: thexvid.com/video/F264FpBDX28/video.html

  • Andrej Cech
    Andrej Cech Year ago

    Dear Leila, many thanks for your channel. Would be possible to sort the drop down list automatically? If there are lot of items, it could be hard to find needed data and would be perfect to have them sorted. Thanks

    • Leila Gharani
      Leila Gharani  Year ago

      Hi Andrej - not yet. We'll have to do it with formulas to have it dynamic. I've added this to my list.

  • Sujeet Kumar Dutta Gupta

    very helpful video ma'am.
    thank u so much...😀😀😀

  • Sukhomoy Bhattacharya

    Hi, This is Sukhomoy a regular follower of your channel. Though I work with BI and data visualisation tools but my first love is still Excel.
    Anyway, after watching this vid wanted to request as a suggestion. The same can be done by OFFSET and INDEX formula with a dynamic approach.
    Where your source list can have n number of Rows and columns.

    • Leila Gharani
      Leila Gharani  Year ago

      Hi Sukhomoy - thank you for your support and the great suggestion. Agree - This can be made more dynamic. ..Offset can also be used here to restrict the number of rows returned so we don't get the empty ones below. Thanks a lot for your comment.

  • Prawin Gr
    Prawin Gr Year ago

    Hi Leila, This is useful and can be used for one dependent. But can you please post a video on how to make multiple dependent lists?

    • Prawin Gr
      Prawin Gr Year ago

      No. Not yet.

    • Kemmerly Erazo
      Kemmerly Erazo Year ago

      Did you find a solution for the case you mentioned?, I´m looking for that solution

    • Leila Gharani
      Leila Gharani  Year ago +1

      Understood. That's a good one. Will add to my video list. Thanks for the suggestion.

    • Prawin Gr
      Prawin Gr Year ago

      Thanks for your response. To be precise in my request, in your above video 'Misty Wash' is dependent on 'Utility Div'. My request is, for example the next dependent should be based on 'Misty Wash' & 'Utility Div'. Like if 'Misty Wash' & 'Utility Div' is selected then 3rd dependent should show 'X' and if 'Misty Wash' & 'Twenty 20' is selected then 3rd dependent should show 'Y', etc., likewise for all.

    • Leila Gharani
      Leila Gharani  Year ago

      Thanks Prawin for your comment. Sure, I'll add this to my list. Multiple dependent lists would work in a very similar way as the first dependent list though.... unless your data is not structured like this....

  • Ahmed Elgammal
    Ahmed Elgammal Year ago

    Many many thanks Leila good job and clear explanation
    Thanks again

  • Adelaida G
    Adelaida G Year ago

    I have a question regar ding a dropdown list before the 1st list. So, I'm trying to include a list for resource. Let's say Resource 1 and Resource 2. Depending on what the choice is, then I need it to open up Code Sections. After that, I need to have specific option of the Code.

    • Leila Gharani
      Leila Gharani  Year ago

      Hi - somehow I missed your reply here. Did you try to download the workbook from the link provided? if you open it up in your Excel and change the headers to your headers - does it work?

    • Adelaida G
      Adelaida G Year ago

      Leila Gharani This didn't work. Please help!! I have 3 choices in my first drop down list identified as R&T codes, rules, annotations. This is all legal codes so it has extensive wording. Excel keeps giving me an error code using index and match

    • Leila Gharani
      Leila Gharani  Year ago

      That sounds like 2 dependent drop-downs. The process is the same, except you will have names instead of numbers. Check out this videothexvid.com/video/w7cHgsFirLk/video.html - it might help.

  • Mario Limoges
    Mario Limoges Year ago

    love it thanks !!