Drop Down Lists in Excel - Masterclass (incl. Dynamic, Dependent & Searchable Drop Down Lists)

แชร์
ฝัง
  • เผยแพร่เมื่อ 22 พ.ย. 2024

ความคิดเห็น • 203

  • @theofficelab
    @theofficelab  4 ปีที่แล้ว +17

    Data is everywhere! Enhance your career and acquire new skills by taking a course on DataCamp! Click here to take the first chapter of any course for FREE: bit.ly/3e03pKO (you’ll be supporting my channel too!)

    • @cesaramir
      @cesaramir 3 ปีที่แล้ว +3

      Hi. . . I was doing the Gantt Chart video tutorial but I notice the video is down . . . can you upload it again???

  • @Pontiki1977
    @Pontiki1977 2 ปีที่แล้ว +9

    Excellent tutorial. 17 minutes in and with that Offset and Counta formula I was extremely confused for some reason, but I finally managed to recreate what you did.
    Thank you so much for this. Pure gold. People actually pay good cash to learn such stuff. Instant sub.

  • @matthewdunn7709
    @matthewdunn7709 ปีที่แล้ว

    I have been trying to figure out searchable dropdown lists for a while.... this tutorial brought an end to that endeavor. Instant subscriber!

  • @stefan66
    @stefan66 ปีที่แล้ว

    Danke Marco für dieses unglaublich hilfreiche Tutorial. Das wird meine inzwischen sehr kompliziert gewordenen Excel-Setups erheblich vereinfachen. Vielen Dank!

  • @markhuang368
    @markhuang368 2 ปีที่แล้ว +1

    I am really grateful for the introduction of dynamic dependant drop down list. I have been confused by the problem for more than two years and now I find the answer.

  • @EduardoBicelisGarcia
    @EduardoBicelisGarcia 3 ปีที่แล้ว +65

    Min 29:37 vlookup(); min 33:13 vlookup() advanced; min 39:56 MultilevelDependant; min 45:05 searchable;

  • @AndreaUK1973
    @AndreaUK1973 2 ปีที่แล้ว +1

    Thank you!!! I was looking for Searchable Drop Down List and you gave me heaven!! You have no idea how much this is going to make my hard daily work much more enjoyable! I’ll never forget you and your skills shared!!!!!

  • @narcisviviana4923
    @narcisviviana4923 4 ปีที่แล้ว +9

    I've been using Excel for alot of years now and, man, it feels so good to learn new stuff! Txs alot for your time and effort!

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      My pleasure 😊 Thanks for your positive feedback!

  • @erincampbell5831
    @erincampbell5831 3 ปีที่แล้ว +1

    Thank you! After watching so many different methods in other videos, this one finally worked for me!

  • @joelbravo2654
    @joelbravo2654 3 ปีที่แล้ว

    Hi,

    My name is Joel and I’ll start off by saying thank you for all of your help this far. I’ve watched your video tutorials, but I had questions and needed help. I hope this catches your interest and you can help me. I know that everyone is busy with their own stuff and if you can’t help me I understand.

    So I’ll start off by describing what I would like to do, much like what you did in some of your tutorials except I don’t know how to apply it to my workbook.
    I have a main page that needs to pull data from different sheets, 220 or so. The way it works is as follows; you select your SPEC from the data validation cell, after that the CATEGORY within that SPEC, then your CHOICES should show.
    I’ve gotten this to work up to the category part. I am stuck on making the “CHOICES Data Validation” part, how to make the choices show since they are in different sheets.

    That’s my most important part I need to figure out. I’ve only populated the first 46 sheets as I didn’t know if this format will even work.
    My second problem is that is that I have been extracting the data from other workbooks by creating a formula in each and every single one then copying and pasting it in their designated spec sheet. Which by the way takes for ever as I have only done 46 of 220 of them. There’s got to be an easier way to do this if not I’ll just keep copying and pasting. I am an excel beginner.

    I would greatly appreciate your help on this and again thanks for all of help this far your tutorials are amazing.

    Bravo
    joelbravo11@gmail.com

  • @i3oomiizz
    @i3oomiizz 2 ปีที่แล้ว +1

    Your explanation is crystal clear! Not fast, Not slow, I loved it!

  • @querrythis
    @querrythis ปีที่แล้ว

    Good tutorial. Easily understood. The searchable dropdown, a winner. Thanks for your help

  • @menow1893
    @menow1893 2 ปีที่แล้ว +1

    A great lesson for me! Thank you so much.

  • @msladytsunade777
    @msladytsunade777 3 ปีที่แล้ว +1

    I'm an Excel Addict! Love this course, using Office 365 and it's great!

  • @yogeshdewange
    @yogeshdewange 3 ปีที่แล้ว

    Sir this is the best video I have ever seen. Thanks

  • @syairol
    @syairol 4 ปีที่แล้ว +1

    Thanks for a very detail and concise explanation of dropdown in excel

  • @RichardTschumi-h4h
    @RichardTschumi-h4h 11 หลายเดือนก่อน

    Excellent tutorial. An enhancment for users would be to add an entry in a dropdown which is actually not included in the data-tables. The system should ask whether to add the new entry or cancel.
    Would be hapy to get a feedback from anyone that has a solution for above issue.

  • @AmitChandra_YouTube
    @AmitChandra_YouTube 3 ปีที่แล้ว

    Wonderful Trick the last one, the searchable drop down list, very much helpful for older excel like Office 2019. Thank you so much.

  • @alakeshb6486
    @alakeshb6486 4 หลายเดือนก่อน

    Awesome class, i need more practice, thank you for your support

  • @jhuanosu96
    @jhuanosu96 3 ปีที่แล้ว +2

    Thanks for sharing this wonderful information, especially the searchable drop down list. What an eye opener!

  • @dani28MSc
    @dani28MSc 3 ปีที่แล้ว +2

    How super useful is this video that you made. Thanks for posting things like this! My 5* to you!! Dan
    xx

  • @KambizSayari
    @KambizSayari 3 ปีที่แล้ว +2

    God bless you for making this I sincerely appreciate you! Thank you!

  • @Chef-1707
    @Chef-1707 4 ปีที่แล้ว +2

    Fantastic really helpful video......easy to follow and well presented....appreciate you taking the time to share

  • @Shoeb._.248G
    @Shoeb._.248G 2 ปีที่แล้ว +1

    Super expert level
    Saleem

  • @YTantirungrotechai
    @YTantirungrotechai 3 ปีที่แล้ว

    Your vdo really blows my mind. Wow, amazing how excel can do.

  • @davidnewton3064
    @davidnewton3064 2 หลายเดือนก่อน

    Excellent tutorial even 3 years later. The one addition I would have liked to see is error correction for the dependent drop down section. If Regions and Country are currently selected and the Region is changed, the Country remains the previous selection. This results in a selected Country not matching the Region. Needs to have something like an OnChange set to "" on that Countries cell.

  • @jsolivas1516
    @jsolivas1516 3 ปีที่แล้ว

    I was always Shit when it come to school and learning, I'm rather dumb and just cant be bothered with school again, but thanks to your videos I am able to still get good jobs for the skills i picked up from you, I really thank you a lot!!

  • @alaabdullah1254
    @alaabdullah1254 3 ปีที่แล้ว +1

    One of the best videos in this topic

  • @atiweetanongsak8739
    @atiweetanongsak8739 3 ปีที่แล้ว +6

    Could you please make an addition tutorial on how to achieve multiple searchable drop down in many rows without VBA? Currently our office still use Excel 2016...

    • @jeetu2812
      @jeetu2812 3 ปีที่แล้ว +1

      My query is same, kindly help.

    • @johnpatrick4185
      @johnpatrick4185 3 ปีที่แล้ว

      Yes... Thank you for your free tutorial but would have been good if you would have mentioned that Searchable Lookup can only be done on one row. (in other words lookup on 2nd row would require a whole new lookup construct)

    • @roxie3121
      @roxie3121 3 ปีที่แล้ว

      Same

  • @excelmastervba
    @excelmastervba 3 ปีที่แล้ว

    Dropdowns are very important. Thank you! Very Good Video. 🙏🚀🚀

  • @vikaasb2016
    @vikaasb2016 2 ปีที่แล้ว

    this is simply sublime

  • @spreadmanic
    @spreadmanic 3 ปีที่แล้ว

    Excellent tutorial, thanks

  • @robhaman4424
    @robhaman4424 4 ปีที่แล้ว +3

    It's also possible to make the Named Ranges dynamic with the =OFFSET(Sheet1!$F4,1,,COUNTA(Sheet1!F:F)-1), use this method for many years with succes.

  • @abeerattia4523
    @abeerattia4523 ปีที่แล้ว

    excellent video , learnt a lot

  • @digikaininja5
    @digikaininja5 3 ปีที่แล้ว

    Thank you for the master class. Just kindly put time stamps for each skill covered. Thanx

  • @FreeD00M
    @FreeD00M 3 ปีที่แล้ว

    Thank you very much. A quick feedback. Timestamps would be super helpful to jump to certain chapters.

  • @andyanand9459
    @andyanand9459 3 ปีที่แล้ว +1

    Hi. I really love your work. Would you please put a class together on Dashboard design

  • @contextmatters8243
    @contextmatters8243 7 หลายเดือนก่อน

    Good Stuff! 🙂
    My only concern is that the coloring using "Conditional Formatting" on by the "Team Members" list
    and the "Assigned To" list don't seem to be connected; therefore, they could be out of synch.
    I suppose you could use VBA to remedy this (maybe -- need to think about it), otherwise I don't see the usefulness of have the color coding in both places
    Although
    I do like it on the "Task Assignment" list...
    Thoughts?

  • @not1AM
    @not1AM 3 ปีที่แล้ว +1

    Maaan.. You really made a great video here.. The searchable list was an issue for me.. Big Thanks

    • @theofficelab
      @theofficelab  3 ปีที่แล้ว +1

      My pleasure 😊 Glad it was helpful.

  • @akkintouch
    @akkintouch 3 ปีที่แล้ว +1

    Hi, thank you very much for this information, this is great
    Regarding Searchable Dropdown, it is only possible for a single cell?
    is it possible to add a dropdown to the number of cells in a column? hope you could help

  • @allenschroeder3660
    @allenschroeder3660 3 ปีที่แล้ว

    Great tutorial

  • @asif3O
    @asif3O 4 ปีที่แล้ว +2

    Thats awesome!. Really appreciate the knowledge you shared. Keep going👍

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      Thanks for positive feedback, Asif!

  • @maximebloquert5661
    @maximebloquert5661 4 หลายเดือนก่อน

    This is a great tutorial!
    15'07: referring to Table name under quotation marks does not work for me...are there are specific parameters to set up in Excel for this to work? Using D365 Office.

  • @aviyzhanouj6946
    @aviyzhanouj6946 ปีที่แล้ว

    Awesome! Thnk u so much sir for this❤

  • @roldangeduquiojr.162
    @roldangeduquiojr.162 3 ปีที่แล้ว

    This has helped my job a lot thank you

  • @catherinewang9017
    @catherinewang9017 ปีที่แล้ว

    This is life changing

  • @jamiljokhyo
    @jamiljokhyo 4 ปีที่แล้ว +1

    Marvellous, time saving formulas

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      Thanks for your positive feedback, Jamil!

  • @IrateHarry
    @IrateHarry 3 ปีที่แล้ว +1

    Brilliant - thanks!

    • @theofficelab
      @theofficelab  3 ปีที่แล้ว

      Thank you for your positive feedback 😊 Appreciate it.

  • @lisaklein4857
    @lisaklein4857 3 ปีที่แล้ว

    Love the tutorial

  • @yeraldo05
    @yeraldo05 4 ปีที่แล้ว +3

    43:42 You don't need to modify the Name Manager's names since Dinamic Tables will update the range everytime when you add new data.
    Other than that really great video on how to Master the Drop Down feature.
    Keep it going!

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      Thank you for your feedback, Yeriel 😊

  • @ellafrost2824
    @ellafrost2824 2 ปีที่แล้ว +1

    This tutorial is literally saving my life right now. I think I was on the verge of a panic attack trying to figure all this out.

  • @teoxengineer
    @teoxengineer 4 ปีที่แล้ว +7

    Also, we can generate dynamic drop down list with using name manager, which consists of table name because table name is not working in list section.

  • @fragilelove
    @fragilelove 4 ปีที่แล้ว +2

    Thanks for this informative video on the Filter Function but I've been struggling for weeks trying to implement it in my example. I have multiple dependent drop down lists and the list data is in a separate sheet, how to I then reference the second dependent drop down if the drop down is in another sheet and multiple rows? Do I reference it when I create a unique list in the filter function or do I do it in the validation? Any help would be much appreciated folks as it has been driving me crazy for weeks now hehehe

  • @E-realmServices
    @E-realmServices ปีที่แล้ว +1

    I have a range of cells which needed to be validated for Searchable Dropdown Lists instead of a single cell. Can you please share modified version of the formula. ?

    • @osoriomatucurane9511
      @osoriomatucurane9511 5 หลายเดือนก่อน

      I am also looking for a similar situation instead of one single cell. A typical and comun scenario of data entry with a lot of records.
      Please share the solution.
      Thank u

  • @caroleroyer5347
    @caroleroyer5347 3 ปีที่แล้ว

    Thank you so much, you’re video was very helpful!

  • @mohamedadjal8502
    @mohamedadjal8502 3 ปีที่แล้ว

    thank you for this video

  • @aritonbogdan5351
    @aritonbogdan5351 3 ปีที่แล้ว +3

    Boss, can u make that droplist expand without click it, just tiping in the cell?

  • @sunilaryan
    @sunilaryan 3 ปีที่แล้ว +1

    Thanks, love this stuff.

    • @theofficelab
      @theofficelab  3 ปีที่แล้ว +1

      My pleasure 😊 Glad you like it!

  • @aliresulozturk1134
    @aliresulozturk1134 4 ปีที่แล้ว +1

    all amazing ty so much for share your knowledge

  • @rayleesummers
    @rayleesummers ปีที่แล้ว +1

  • @husseinbdeir9214
    @husseinbdeir9214 3 ปีที่แล้ว

    Amazing!!

  • @rarevidz100
    @rarevidz100 4 ปีที่แล้ว +2

    Another great video...

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      Thank you, Kevin 😊

    • @rarevidz100
      @rarevidz100 4 ปีที่แล้ว

      @@theofficelab hope you could touch on power BI, I really cannot with that app.. But it looks great to work with

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      Power BI is one of the topics at the top of my list! So stay tuned.

  • @mehmetulvibayoglu2350
    @mehmetulvibayoglu2350 10 หลายเดือนก่อน

    Thanks!

  • @wixex
    @wixex 2 ปีที่แล้ว +1

    dependent drop downs, worked for some of mine, and some it didn't. Can't really figure out why -.-

  • @SATYAPRAKASH-xz5qb
    @SATYAPRAKASH-xz5qb 3 ปีที่แล้ว

    Thank you brother

  • @ubaidillahmuhammad20
    @ubaidillahmuhammad20 4 ปีที่แล้ว +2

    Nice job. Put iits excel Files in the description....

  • @GM-js6jn
    @GM-js6jn 2 ปีที่แล้ว

    Thank you. Is it possible to have multiple selections in the drop down list, and the dependant list provides appropriate options?
    E.g. in your example if I want to select both Asia and Europe, and subsequently China, Japan, Germany, UK? I've been stuck on this for a while now.

  • @dema-3000
    @dema-3000 3 ปีที่แล้ว +1

    nice !!!, but how we can do dependent drop down if the data is on another workbook?

  • @redhaakhund1271
    @redhaakhund1271 3 ปีที่แล้ว

    Thanks for the video, may I have a copy of this exercise please.

  • @ryanpeterson7998
    @ryanpeterson7998 3 ปีที่แล้ว

    WONDERFUL!!!!!!!!!!!!! is it possible to do everything in this video but tweak it???? I need it to only pick a name on the list, currently the user can type in any name in the cell and it will accept it. i am looking for a way to still sort (as seen in the video) but make it pick from the list with zero exceptions. tried turning on the data validation error back on but then the sort doesn't work. PLEASE HELP

  • @mojtabafazeli2140
    @mojtabafazeli2140 3 ปีที่แล้ว

    Hello, thank you for your very beautiful tutorials .. Is it possible to put a tutorial of a login page in PowerPoint? ... Thank you.

  • @behindthescene2727
    @behindthescene2727 3 ปีที่แล้ว

    solid #DropDownList tutorial

  • @syedimran9406
    @syedimran9406 ปีที่แล้ว

    Great sir one more thing how can I apply same searchable list on other cell thanks

  • @AshokKumar-fu5ze
    @AshokKumar-fu5ze 3 ปีที่แล้ว +1

    Great video 👍. Have a doubt.. what if we have to use the searchable drop down list in every row as a part of a table ? Do we need to put a helper column to filter the search?

    • @roxie3121
      @roxie3121 3 ปีที่แล้ว

      This is my exact problem, now. (I followed the instruction and everything worked out great up to that point/ that need.)

  • @singhalmonica
    @singhalmonica 3 ปีที่แล้ว

    Thanks sir for great lessons. Sir my task list keep changing, I want to show drop down only for show tasks...pls help me for this.

  • @jeffreyyao3923
    @jeffreyyao3923 3 ปีที่แล้ว

    Great tutorial ! Can you please share the Excel file used in this video?

  • @wisnuprabawa2907
    @wisnuprabawa2907 3 ปีที่แล้ว

    big thanks for this video,,, regards from Indonesia. :)

  • @wenjing712
    @wenjing712 2 ปีที่แล้ว +1

    How to add searchable drop down list for multiple row in excel 2010?

  • @mirrrvelll5164
    @mirrrvelll5164 3 ปีที่แล้ว

    Great video man! Got a workbook also for this?

  • @luisrave4785
    @luisrave4785 3 ปีที่แล้ว +1

    Hi, there was a video of manager the task where you can put a start date and a end date and the conditions format show the line in color.
    Do you remove it, becouse I didn't find it!

  • @juliayeo812
    @juliayeo812 2 ปีที่แล้ว

    hello! how do I get my 2nd level of drop-down list be automatically indicated when I choose the 1st level of drop-down list?
    Thank you so much for you advice!

  • @Piola91
    @Piola91 3 ปีที่แล้ว +3

    Hey, what happened to the "ultimate Gantt chart for project management" video?? please, I beg you to upload the video again!! pleaseeeeeee

    • @Noveed
      @Noveed 3 ปีที่แล้ว

      I 2nd this too. I had it saved to pick up and watch at a later date however now it has disappeared :(

  • @omotossso
    @omotossso 4 ปีที่แล้ว +2

    I want to ask if I will have access to the new functions and formulas in MS Excel if I purchase any package of the Microsoft 365
    I love your videos. I've learnt a lot

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      Yes, these new functions should be available in any Microsoft 365 subscription.

  • @singhalmonica
    @singhalmonica 3 ปีที่แล้ว +1

    Sir, if we delete some task from list(Column B), than drop down will remain in the cells(Column C). Is this possible to remove drop down settings also for Column C

  • @neerajswamy864
    @neerajswamy864 ปีที่แล้ว

    How did you get such nice colors in Fill tab ? Can you tell me how to get those ?

  • @seiferflo24
    @seiferflo24 3 ปีที่แล้ว +2

    First time I follow such a well explained tutorial on Excel. I appreciate the fact you're showing different ways of doing the same thing and that you keep adding to it, really understanding each feature.
    However, I have an issue with the dynamic dropdown list (using offset or not). You're saying at 5:30 that if you rename an item in the "list" it gets updated on the first "sheet". This is only half-true because if you've already selected an item for your sheet, ket's say in your example projet A has "Purchasing" selected, and you modify the name in the list to "Purchase", then your sheet won't show the update... It will get stuck to Purchasing on projet A because it was already selected.
    In my case, this is what's happening, so it's pretty annoying as there is only a link to the cell but not it's content, so I have to make sure my list never changes in terms of terminology. Any idea please? Thx for your help

  • @Rice0987
    @Rice0987 4 ปีที่แล้ว +5

    Couple month ago I've made multi-level drop list and today i dont understand how i did it. :)

  • @UU-ry6gt
    @UU-ry6gt 3 ปีที่แล้ว

    Great tutorial! Any idea how make it dynamic for search cell (b5 in your case)?

  • @markmcpeake715
    @markmcpeake715 3 ปีที่แล้ว +2

    UNIQUE function first mentioned at 35:26 isn't available in 2019 or earlier, only Office 365. Maybe should have mentioned this at the beginning of video.

  • @putojarijari4258
    @putojarijari4258 4 ปีที่แล้ว

    I love these videos! After a tutorial you did I made a sheet that tracks all sorts of Instagram Statistics.
    My question is, is it possible or do you have a tutorial about fetching data from a website and implementing
    it into the Excel sheet? For instance how many followers you have or the amount of posts you got on said website.

  • @aimalpoya1978
    @aimalpoya1978 3 ปีที่แล้ว

    Great!

  • @ronaldremollena4300
    @ronaldremollena4300 ปีที่แล้ว +1

    how can I replicate the searchable dropdown to other cell?

  • @sujamahesh2494
    @sujamahesh2494 3 ปีที่แล้ว

    Can you show me how to calculate average waiting times with 2 different ranges of dates by week

  • @stephaniemcsweeney9153
    @stephaniemcsweeney9153 3 ปีที่แล้ว

    How do I create a sheet with multiple dependent columns? I have 4 columns: Column A trickles down to Column B, Column B then trickles down to Column C, and lastly Column C trickles down to Column D. Each Column can contain duplicate values or unique ones. I need to be able to have the pick lists filter down to the relevant choices based on the previous selection

  • @bt8633
    @bt8633 4 ปีที่แล้ว +3

    33:04 great thx

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว

      My pleasure 😊 glad you found it useful

  • @AlbertDLinis
    @AlbertDLinis 3 ปีที่แล้ว

    What if you delete the data in the middle of the list? How will it remove the blank space?
    Thanks!

  • @benabdullahzouhaier1035
    @benabdullahzouhaier1035 3 ปีที่แล้ว +1

    Can we apply this Combine Drop list with number or timing , for calculation of the next appointment example ?
    I tried many time for fix timing (15 min, 30 min and 1 Hr) to calculate the next schedule but i didn’t succeed......

  • @Tuber-n7c
    @Tuber-n7c 6 หลายเดือนก่อน

    great tutorial BUT in office 365 i only have FILTERXML function no FILTER function - whats up with that can you advise please?

  • @michaelsvenson2456
    @michaelsvenson2456 3 ปีที่แล้ว

    Great video on how to use Offset and CountA, when I try this in my excel it does'nt work, I only see the top row "Michael" and nothing else....

  • @rahulchoudhury2991
    @rahulchoudhury2991 4 ปีที่แล้ว +2

    Hi, would you mind showing how to use an Excel to create Crossword Puzzles with design. Would be pretty fun to know!

    • @theofficelab
      @theofficelab  4 ปีที่แล้ว +2

      Hi Rahul.
      Thanks for your suggestion 😊 Sounds fun, but regarding Excel I decided put the focus of this channel on topics that can be used in a business context. Hope you understand.