Smart Dep. Drop-Down Lists in Excel: Expandable & Exclude Blank Cells

Share
Embed
  • Published on Jul 19, 2018
  • Create drop-down lists in Excel that are dynamic - #1 They expand as new data is added to the list, #2, they are dependent on another list and #3, they exclude blank/empty cells on the bottom of each list. Basically they are only restricted to the cells that have values in them.
    In this video you'll learn how to use the OFFSET function to get fully dynamic dependent drop down lists.
    You'll then see another version which uses INDEX & MATCH as well as Excel Tables. We plug in the formula we create inside Name Manager and then use the new name inside our custom data validation formula box.

    🡻 Download the workbook here 🡻
    www.xelplus.com/excel-dynamic-dependent-drop-down-lists-exclude-blanks/
    ★ Links to related videos: ★
    Data Validation drop down Playlist: thexvid.com/p/PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y
    Learn OFFSET: thexvid.com/video/RPTQjbk2qy4/video.html
    Learn INDEX & Match: thexvid.com/video/F264FpBDX28/video.html
    ★ My Online Excel Courses ★
    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 • 269

  • Quetzal Chapa Zapata

    I use tables and INDIRECT() , to the trick to work the list must have the same name as the header, it also work with named ranges

  • John Mantonya
    John Mantonya 5 days ago

    Dear Leila Gharani

    I need a Spreadsheet or Database that does this, please see attached spreadsheet for example.
    (need an email address to send the example to you - mine is johnmantonya@outlook.com)

    I wanted a spreadsheet / database that would occupy 6 email fields in just one column ...

    All I need is the model or template since I dont know how to create such a tool.
    1. I Have all the STATES...
    2. I Have all the COUNTIES for each STATE...
    3. I Will choose the CITIES to go with the COUNTIES...
    4. I Will choose the CHURCH NAMES, I plan to put at least 5 CHURCH NAMES per COUNTY...

    All I need is a Spreadsheet or Database working model I can Populate...or Data Entry...

    A spreadsheet / database that:
    1. By Clicking on "STATE" would provide/populate all its "County"s

    2. By Clicking on "COUNTY" would provide/populate all its "City"s

    3. By clicking on "CITY" would provide/populate all its "Church Name"s

    4. By Clicking on "CHURCH NAME" would provide the 23 data fields to be filled in from "Church Phone" to "Secretary Name" in a Column; and in another column the 6 email fields from "Office Emal" to "Secretary Email" ...

    Can you help or put me in the right direction?

  • Hazem Ali
    Hazem Ali 9 days ago

    WOw great

  • Tshepo Moleko
    Tshepo Moleko 10 days ago

    If A1="NA", B1 must return 0 and lock to prevent any value entry. However, if A1="A", B1 must unlock to allow value entry from any of the specified values chosen from a drop down menu. Let's say 0,2,4 and 6 are such specified values. Please help me.

  • Evgenia M
    Evgenia M 10 days ago

    WOW! Masterfully mindblowing !! Thanks a lot Leila :)

    • Leila Gharani
      Leila Gharani  10 days ago

      I'm glad you like it Evgenia! Hope it will be helpful for you.

  • Jim Somera
    Jim Somera 12 days ago

    You are a lifesaver! I used the offset method in creating drop-down list with 198 tables as reference and it worked like magic! Thank you Leila! ❤️

  • Afzaal Ahmed
    Afzaal Ahmed 14 days ago

    wonderful work, i admire you really

  • vijay sahal
    vijay sahal 15 days ago +1

    super duper 👍

  • SmarT Odd
    SmarT Odd 26 days ago

    Your voice is so very "comforting" - great video

  • Mike Aikens
    Mike Aikens 27 days ago +1

    Your videos are great! Thank you for all you do. Is it possible to add data to the source list by typing into the combo-box? Thank you!

  • Yoosuf Danway
    Yoosuf Danway 28 days ago

    Wonderful video, could you please tell me how to create a dropdown list with new data entry in the same field.

  • Karen Hutchinson
    Karen Hutchinson 29 days ago

    Hi Leila, Yet another great video, clear and easy to follow. Thanks.
    I'm testing the functionality now on one of my workbooks.

    • Leila Gharani
      Leila Gharani  28 days ago

      I'm glad to hear that Karen. It's great you can apply it right away.

  • Jun Ramos
    Jun Ramos Month ago

    What if i select productivity(productivity, games and utility is the only drop down) and it will show list below in table form just like the source

  • Vijay Pawar
    Vijay Pawar Month ago

    Is it possible to showcase cone of uncertainty graph in Excel and should be dynamic

    • Vijay Pawar
      Vijay Pawar Month ago

      @Leila Gharani Thanks

    • Leila Gharani
      Leila Gharani  Month ago

      I don't have a video on it but you can definitely do fan charts in Excel. There are already solutions out there like from my MVP colleague Jon Peltier.

  • Razeen Ahmed
    Razeen Ahmed Month ago

    How to do dependent list in some other worksheets?

  • Zam's Place
    Zam's Place Month ago

    hi Ms. Leila.. your tutorials are great. May I know what tool you use in recording it :)?

    • Leila Gharani
      Leila Gharani  Month ago

      Mostly Camtasia. You can check out my recording gear here: www.xelplus.com/resources/#gear

  • Binod Thakur
    Binod Thakur Month ago

    So helpful to me.. Thank You Mam.

  • Jason Guo
    Jason Guo Month ago

    How can you expand the Productivity/Games/Uility in Table method? You have to offset in order to expand laterally?

  • Md.Abdul Kader
    Md.Abdul Kader Month ago

    Pharmacy Prescription Create With searchable List........ How Can I Create?

    • Leila Gharani
      Leila Gharani  Month ago

      If you have Dynamic Arrays very easily like this: thexvid.com/video/Z-h2UER3b_0/video.html

  • sn.raihan Exports
    sn.raihan Exports 2 months ago

    can we make in Excel when we type drop down list is automatically drop down and display the list ?

    • Leila Gharani
      Leila Gharani  2 months ago

      Here is a VBA free version with data validation: thexvid.com/video/Z-h2UER3b_0/video.html To get it fully automatic you'll need vba

  • Luis Ferreira
    Luis Ferreira 2 months ago

    It is a great video, however as Josh Parker as mentionned we are getting a #N/A message, plus, in my case, I m not allow to use , I need to use ; , but that I guess is related with the Country. It would be nice to have some answer/support here. I will appreciate. Regards

    • Leila Gharani
      Leila Gharani  2 months ago +1

      The separator is dependent on your regional setting. It works if the source data is on another sheet. You can download the workbook for the video from the description of the video. It takes you to the blog post, scroll down, and download). If you use the offset version though you need to make sure to update the worksheet reference.

  • Petra Paulina Emotera
    Petra Paulina Emotera 2 months ago

    Can you make this a searchable dropdown?

    • Leila Gharani
      Leila Gharani  2 months ago

      Yes, with the new dynamic arrays (only in Office 365): thexvid.com/video/Z-h2UER3b_0/video.html

  • Mohit Singh
    Mohit Singh 2 months ago

    Excellent...
    Please upload videos on dashboards....

    • Leila Gharani
      Leila Gharani  2 months ago

      Glad you like it. I have a whole course on dashboards actually.

  • tanjirul haque
    tanjirul haque 2 months ago

    All of your videos are outstanding. Please keep up your great work. Thanx again.

    • Leila Gharani
      Leila Gharani  2 months ago

      Thank you for the kind words. I'm glad you like the videos.

  • tanjirul haque
    tanjirul haque 2 months ago

    Thank You very much. God bless you.

  • Tressagail Gaming
    Tressagail Gaming 2 months ago

    Hi Leila, i have a questions regarding this formula and the application to the spreadsheet that i am currently writing. i have a range of data sorted and indexed by this formula: =IF(ROWS(D$143:D143)>$D$101,"",INDEX($D$103:$D$139,SMALL(IF(FREQUENCY(IF($D$103:$D$139"",MATCH($D$103:$D$139,$D$103:$D$139,0)),ROW($D$103:$D$139)-ROW($D$103)+1),ROW($D$103:$D$139)-ROW($D$103)+1),ROWS(D$143:D143)))). your video above has solved all of my problems baring one. the drop down list now created has blanks at the bottom due to the sorting formula that puts a "" in the blank cells. this is being counted in the formula in your video. how could i amend the formula to remove these blanks from the drop down lists that have been created? the current formula i am using is: =OFFSET(Matrix3.3SWIP!$D$142,1,MATCH('steve WIP'!E3,Matrix3.3SWIP!D142:AA142,0)-1,COUNTA(OFFSET(Matrix3.3SWIP!$D$142,1,MATCH('steve WIP'!E3,Matrix3.3SWIP!D142:AA142,0)-1,25),1))
    would appreciate any help you can give me here. i have been trying to get this to work for a couple of days now.
    Thanks
    Steve

  • REDA MERBOUNI
    REDA MERBOUNI 2 months ago

    Really i loved this...

  • Carlos Castillo Abreu
    Carlos Castillo Abreu 2 months ago +1

    Awesome tuts! I tried the 1st method, but when dropping down the list, it shows me values repeated, and they're not repeated on my table. How can it only show unique? Thanks

  • Miguel Cayazaya
    Miguel Cayazaya 2 months ago

    I wont like this explanations. When the speaker keeps on adding layer after layer of information, without completing cycles is very taxing on working memory. Generally I follow the instructions but this one isn't something I can follow too well. I will take a nap and comeback to it :)

  • Mohammad Eshaq
    Mohammad Eshaq 2 months ago

    Wonderful explanation. Keep up good work.

  • Josh Parker
    Josh Parker 2 months ago

    I love the idea of this but I cannot get the formulas to work out for me. My second dropdown box is returning a #N/A error. Is this because I have the tables I am referencing with INDEX on a separate sheet in the same workbook?

    • Luis Ferreira
      Luis Ferreira 2 months ago

      @Leila Gharani Thank you Leila by the quick reply, I will give a try.

    • Leila Gharani
      Leila Gharani  2 months ago +1

      It works if the source data is on another sheet. You can download the workbook for the video from the description of the video. It takes you to the blog post, scroll down, and download). If you use the offset version though you need to make sure to update the worksheet reference.

    • Luis Ferreira
      Luis Ferreira 2 months ago

      I have the same issue and aparently I am also doing what you have done. I have created a unique workbook and the problem maintain the same...Another think is when I use , I got a message error and is not acceptable and when I switch to ; I can write exactly what it is showed in the video but at the end i get the message #N/A

  • wooly samoan
    wooly samoan 2 months ago

    WTF?

  • Kumar Rohit
    Kumar Rohit 2 months ago

    Thank you so much! You just made my day 🙂

    • Leila Gharani
      Leila Gharani  2 months ago

      You're most welcome. Glad you like the video.

  • Feroz Khan
    Feroz Khan 2 months ago +1

    how to summarize the datas of my parent drop down and its dependent drop down?

  • Peter Ng'ang'a
    Peter Ng'ang'a 2 months ago

    Thanks Leila. Thanks.

  • Ren Sycloren
    Ren Sycloren 2 months ago

    Great explanation.....!!!

  • Karthikeyan Subramanyan

    Leila, in your original video “offset for dynamic arrays” you didn’t use a secondary offset within the counta function. Even here I think you can do the same .

  • Mohamed Selim
    Mohamed Selim 3 months ago

    thexvid.com/video/T3kWIw9sjL8/video.html
    But it's not as easy as the data validation - list
    Thanks

  • Ruisandro Tromp
    Ruisandro Tromp 3 months ago

    Thanks, very clear and simple explanation. Is there a way to create a dropdown list from items in another excel file and each you select 1 of these items it substract the quantaty you select in the template?

  • Gera Sanz
    Gera Sanz 3 months ago

    I just love your videos and tips nice work! And greetings from México

    • Leila Gharani
      Leila Gharani  3 months ago

      I'm glad to hear that! Greetings to México.

  • Annie Nielson
    Annie Nielson 3 months ago

    I learned so much, thanks!

  • John Rice
    John Rice 4 months ago

    It's a kind of magic!

    • John Rice
      John Rice 3 months ago

      @Leila Gharani Black excel magic is VBA, but formulas is the white magic. 😊
      Thank you!

    • Leila Gharani
      Leila Gharani  3 months ago

      Black Excel magic :)

  • tarnaka504
    tarnaka504 4 months ago

    excel is a rocket science,
    but you made it like a choclate!!!

    • Alex Granowsky
      Alex Granowsky 2 months ago

      tarnaka504 on god we gon’ get you some pussy bro

  • 1zemaitukas
    1zemaitukas 4 months ago

    Hello Leila,
    I have a more difficult task. I need that drop down list result to be a table. For example, if I choose a phone manufacturer from drop down list, the table shows all models of this phone. Is it possible to do this in excel?

  • sirri tanribilir
    sirri tanribilir 4 months ago

    Very indirect way.. there was a better solution creating these data as tables and using “indirect” formula in data validation..

  • khalid Qweder
    khalid Qweder 4 months ago

    Great Teacher.

  • Nana Ampem Darko
    Nana Ampem Darko 4 months ago

    This is beautiful..... Exactly what I needed. Thanks Leila

    • Leila Gharani
      Leila Gharani  3 months ago

      You're very welcome. I'm glad you like it.

  • Sashank Salvi
    Sashank Salvi 4 months ago

    Hi Leila, I have used the "offset" formula in Excel for drop-down and its functions, but it sometimes works, I have data for the brand and product, when I have select product in the drop-down and when i go to brand dropdown and when I select any brand, the product does not automatically change in the drop-down, it has to be changed manually.

  • Tom Chandler
    Tom Chandler 5 months ago

    I am struggling to master this. My formula is as follows; =OFFSET($A$1,1,MATCH($E$9,$A$1:$C$1,0)-1,1,1) and it works this way. However, if I change the 1 to the right of the -1 to a different number like you have, I receive a VALUE! error. Could you help me with what I am doing wrong?

    • Leila Gharani
      Leila Gharani  5 months ago +1

      The value error doesn't necessarily means it's wrong. It just means Excel can't show so many results in one cell (whereas the data validation can). The best test is to click inside the formula bar and press F9. If you see the correct results there, then the data validation will work - make sure you press Escape or Control Z before leaving the formula box.

  • Michael Pole
    Michael Pole 5 months ago

    fantastic insight very useful stuff! you look hot too! (#saucy)

  • Majeed Ahmad Jafri
    Majeed Ahmad Jafri 5 months ago

    Great

  • Glenn Magcaling
    Glenn Magcaling 5 months ago

    Well done, Ms Gharani! Very informative. Excel users will surely love your tutorials! Keep it up.

  • mostafa4321
    mostafa4321 5 months ago

    Hi Laila I have a question to ask. I am trying to create a multiple selection list using data validation but for a whole column. I created a VBA for one cell but for some reason, it does not propagate to the whole column. I want each cell in a column of 30 rows to have the same selection ( multiple) from the same source. Can you help? Thank you :)

  • ripudaman singh narang
    ripudaman singh narang 6 months ago

    It's difficult to complement you, as I was struggling for this for last couple of months, used table but it wouldn't appear in datavalidation, and what a simple trick u just explained in this video.. kuddos to you and your team

    • Leila Gharani
      Leila Gharani  3 months ago

      You're very welcome! I'm glad I could help with that.

  • Uma Maheshwar Askula
    Uma Maheshwar Askula 6 months ago

    Thank you! Leila

  • phil knott
    phil knott 6 months ago

    this has been a massive help in my analysis - thanks Leila :)

  • Marks Man
    Marks Man 6 months ago

    Leila, congratulations! I am a financial analyst and like your course as well as excel. Your town Vienna is a beautiful city. I wish you all the best with your new plan!

  • Gennifer Caesar
    Gennifer Caesar 6 months ago

    Great tutorial! Does this work having the table list on one sheet and the drop downs appear in a separate sheet or only all in one worksheet?

  • Boyka
    Boyka 6 months ago +1

    Quick questions,
    1. How can I create a searchable drop-down list for multiple cells?
    2. If I add more products or games into the list, do I have to recode the whole list again?
    Thank you

  • IOANNIS SIGALAS
    IOANNIS SIGALAS 6 months ago

    hi I tried to make the depented drop down lists by the table method in excel 2019 but the program found the formula wrong. i did exactly what you did. do you have any idea???