NEW Excel Drop-Down Lists That Adapt to Your Data

Поділитися
Вставка
  • Опубліковано 30 жов 2024

КОМЕНТАРІ • 149

  • @MyOnlineTrainingHub
    @MyOnlineTrainingHub  2 місяці тому +1

    ❓How do you currently manage long lists in Excel? Share your approach!
    Learn Excel with my courses: bit.ly/ddlists24courses

    • @shakiraasfoor7599
      @shakiraasfoor7599 2 місяці тому

      amazing Beter for cell function

    • @shivambhola373
      @shivambhola373 2 місяці тому

      Hi Mynda Treacy, I have does same working in MS Office 2021 but On part of Circular reference drop down list didn't showed up, i can see the drop down button but it didn't clicked like it has been blocked, does that function works only in Excel 365 ?

    • @mshi5826
      @mshi5826 2 місяці тому

      @@shivambhola373 @MyOnlineTraingHub I use Office 2021 too and for Method 2, the Countries duplication didn't remove in drop-down list.

  • @ismailrajiwate9565
    @ismailrajiwate9565 2 місяці тому +8

    Explained the most complicated array and filter functions in a very simple way. Depended drop down list now easy to use. Thank you very much

  • @josh_excel
    @josh_excel 2 місяці тому +12

    I did not know you can add # after the XLOOKUP range like that. Very interesting!

  • @ctibornovotny5591
    @ctibornovotny5591 Місяць тому +1

    This is super useful for me. You can even use it in the same table where are the data for your drop down lists if you allow the user to overwrite data validation.

  • @bigbyrd057
    @bigbyrd057 20 днів тому

    Wow! Mynda, you explained some of the most difficult formulas and made it seem very easy! This video opened my eyes to possibilities I didn't know I had. These methods will definitely save me a lot of time with large data sets when using the drop-down feature. Thank you so much for sharing your knowledge with us.

  • @patrickschardt7724
    @patrickschardt7724 2 місяці тому +4

    I like how easy drop down lists are becoming
    Thanks for the detailed explanation

  • @leelawrence3379
    @leelawrence3379 Місяць тому

    That was pretty cool, so many ideas for it's uses now.
    Your tips on using the filter function and having it spill the results were a game changer in the past.

  • @DecrepidOne
    @DecrepidOne 2 місяці тому

    I love this method. Creating dynamic dependent lists has been painful. This will make it so much easier and reduces the chance of the recipient of the file mucking up the lists. Thank you.

  • @murijansulemana5270
    @murijansulemana5270 2 місяці тому

    This is absolutely beautiful. Struggled setting up a dashboard last week, now i can go back to it 😊

  • @davelaff73
    @davelaff73 Місяць тому

    This is going into my top-ten Excel videos playlist. Nice!

  • @melbruning
    @melbruning 2 місяці тому

    wow, definitely need to practice this one. this is great!

  • @Trucpq
    @Trucpq 2 місяці тому

    It is great ! Thanks for sharing with careful explanation ❤

  • @dkarthikr123
    @dkarthikr123 2 місяці тому

    Incredible! Now I'm going to get thru ur tutorial and move 1 level up my EQ (excel Quotient)

  • @ivanbork4175
    @ivanbork4175 Місяць тому

    You are amazing, the # outside Xlookup I have never seen 😊

  • @mouhammadwasseemshaabani47
    @mouhammadwasseemshaabani47 2 місяці тому +1

    Thanks for the best excel teacher ever , I just hope excel will consider upgrading lists so it will update the selection depending on another list selection , like to put the first option automatically as example when parent dropdown list change, (without using vba)

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Yes, that would be a nice change, but I doubt we'll get it, unfortunately.

    • @BoraHorzaGobuchul
      @BoraHorzaGobuchul 2 місяці тому

      Yes, multilevel dropbox lists should def be included as a standard feature...

  • @theluckcoach4592
    @theluckcoach4592 2 місяці тому +3

    🤯wow that 2nd option!!!

  • @muhammadtambawala6379
    @muhammadtambawala6379 2 місяці тому

    Great video Mynda, thanks.
    The `#` sign at the end of the formula and the cell address are both great.
    The issues I encounter with data validation are:
    A) if the text selected from the validation list is no longer in the source, it doesn’t trigger an error, so it can display text that isn't in the source list. B) Another issue is that someone can copy and paste values from another cell and data validation will still accept it.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому +1

      Yes, unfortunately they're not as robust as they could be.

    • @muhammadtambawala6379
      @muhammadtambawala6379 2 місяці тому

      Hopefully in few years time 😊

    • @muhammadasad729
      @muhammadasad729 2 місяці тому +1

      A conditional format trigger may be what you're looking for. This vid has a method towards the end:
      ua-cam.com/video/BpPOP1FfkP0/v-deo.html
      And that '#' sign. Simply magical. Makes you wonder was it always there the whole time.

  • @jitendratrivedi7889
    @jitendratrivedi7889 2 місяці тому

    As usual amazing video , too much information, explained in a very simple way

  • @Jojo-gf5qb
    @Jojo-gf5qb 2 місяці тому +2

    Great
    Dependent drop down list has become easier!

  • @grimaldasgrydas
    @grimaldasgrydas 2 місяці тому +1

    Thank you for the great video and handy info - very useful as always! :) I have been using the first trick (with a bit different approach) for a while now - a bit messy but works well. I have noticed that sometimes it's convenient to name both lookup arrays, and in that case you can use # for those without having to rely on pre-expansion.
    IMPORTANT NOTE: A word of caution, it may have been worth mentioning, that INDIRECT, OFFSET and CELL (in this case) are all *volatile* functions, so if you are using a huge set of data with the second trick, it's can and likely will cause noticeable lag, especially if you have an older/slower PC. I suppose with the new PCs it's not such a big deal, but I have a powerful PC and some really large tables in some of my projects that can easily cause issues with these functions, especially when nested.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому +1

      Thanks for watching! In this case because we are only using INDIRECT, OFFSET and CELL in one cell, it won't have any performance implications irrespective of the size of the dataset. 😅 If we were to use them in the data validation source, then we could have issues, but thankfully that's not required here.

    • @grimaldasgrydas
      @grimaldasgrydas 2 місяці тому

      @@MyOnlineTrainingHub Thanks for replying! I suppose I was too tired when writing my comment. I should have been more careful to point out that my point should apply only if one applies this to a *huge* set of data (referenced with these functions) AND plenty of drop-downs - just a few there and here probably wouldn't harm the performance unless the references are gigantic or the PC/Mac being old enough to have performance issues easily.
      I have some files where I have tables of some ~8 million cells, hundreds of drop-downs etc. In such cases my PC would certainly hiccup. In such files I'm always using a slightly different version of your first approach (named the references and made use of # so they always stay dynamic to avoid errors), mainly to avoid potential errors like forgetting to update the references when I expand the sources.
      By the way, do you think it could be possible, at least in theory, to add a third layer to the first approach? So far, I have deemed it impossible, but I wonder if there were tricks and workarounds to achieve that. I think I will try experimenting on it, but it will most certainly be inconveniently complicated and messy, if even possible in the first place.
      (Sorry for the lengthy reply 🙈)

  • @yulinliu850
    @yulinliu850 2 місяці тому

    Love it. Thanks for sharing 👍👍❤❤

  • @dougbrown3607
    @dougbrown3607 2 місяці тому

    Brilliant. Thanks for sharing!

  • @sachinkishore4115
    @sachinkishore4115 Місяць тому

    excellent . thank you

  • @chrism9037
    @chrism9037 2 місяці тому

    This is great Mynda!

  • @sahralsahri7180
    @sahralsahri7180 2 місяці тому

    Thanks for your effort🌹

  • @Petrus_Mi
    @Petrus_Mi 2 місяці тому

    This is magic! thanks!

  • @michaelbrown8821
    @michaelbrown8821 2 місяці тому

    Thanks, Mynda. I hadn't seen any videos on this before, but got the same info from comments on a similar dropdown video, which saved my me a ton of work. Thanks for making this so I can now easily reference the technique. I have used this in Tables with little fuss. Any concerns that you can share? Great video!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому +1

      Glad it was helpful! The only concern as with all multi-level drop downs is you can change a selection upstream that no longer relates to the downstream selections, so you may want to add some validation to highlight any occurrences of this.

  • @Excelambda
    @Excelambda 2 місяці тому

    Great video !! Great trick !! ✌🏼
    Remembered, 3 years ago you did "Easy Dynamic Dependent Data Validation Two Ways" and I had this comment with 5 replies:
    To make it fully dynamic adding new countries with their regions :
    DV for Countries at H3#=SORT(UNIQUE(TblRegions[Country]))
    DV for Region at I3#=FILTER(TblRegions[Region],TblRegions[Country]=OFFSET(INDIRECT(CELL("address")),,-1))
    Input data: after you select a country in B4 for example, double click in the cell to the right (C4) , click the drop down , you will get the corespondent regions.
    As long as the region is to the right of country DV range, you can place them anywhere.......................
    😉✌🏼

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому +1

      Yep, I stumbled upon your comment recently and thought I should do a video about that 😁thanks again for sharing it way back then, Peter.

    • @Excelambda
      @Excelambda 2 місяці тому

      @@MyOnlineTrainingHub You are very welcome!!! 🙏🏼🙏🏼
      I am not Peter 😉Cesar is my name !!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Місяць тому +1

      Ah, sincere apologies, Cesar! I don't know why I thought you were Peter. 😳 I've put a note in the video description with a correction.

    • @Excelambda
      @Excelambda Місяць тому

      @@MyOnlineTrainingHub No problem at all !!!✌🏼😉

  • @jorgelara3116
    @jorgelara3116 2 місяці тому

    Thanks! so useful topic ! Gby

  • @ruzaqir354
    @ruzaqir354 2 місяці тому

    Thx for the knowledge

  • @jamiegoodwin6667
    @jamiegoodwin6667 2 місяці тому +3

    FYI AL is the code for Alabama, the code for Alaska is AK.

  • @ianl1052
    @ianl1052 Місяць тому

    Nice one, Mynda (and Bartholomew of course). More Excel magic. I love it.
    I could have downloaded the file, but where's the fun in that? If it had been a large file with hundreds or even thousands of entries, that would have been a different matter, of course. I'm not lazy, but I'm not stupid either. ;-)
    Many thanks for your time and effort in making this video.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Місяць тому +1

      Glad you liked it, Ian!

    • @ianl1052
      @ianl1052 Місяць тому

      @@MyOnlineTrainingHub
      You don't need to worry about me not downloading this particular file. I have downloaded others in the past and am, as a result, on your mailing list.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Місяць тому

      Awesome to hear 😁

  • @dannywood7450
    @dannywood7450 2 місяці тому +1

    I like the double click instead of F9 to change the cell address

  • @TimEllisWW
    @TimEllisWW 2 місяці тому

    That is an interesting one adding the # after the ), what other tricks are out there like this one?

  • @DingusBatus
    @DingusBatus 2 місяці тому

    ❤ an emoji for when words can’t describe it.🤩

  • @haych-u3w
    @haych-u3w Місяць тому

    Hi Mynda, great video.
    How would I add a 4th level based on the other 3?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Місяць тому +1

      Replicate the last example where I created 3 levels and make a 4th. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @kylelane630
    @kylelane630 2 місяці тому

    The issue I always had, and resorted to the old convoluted method, was due to the use of the INDIRECT function in the second list in Data Validation (and therefore range names) was you couldn't have spaces in your visible text e.g. Primary_Function

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Hopefully, you can make use of one of the other methods that don't use INDIRECT.

    • @arpwable
      @arpwable 21 день тому

      I used to get round that by nesting a SUBSTITUTE function within the INDIRECT to replace spaces with underscores so that the INDIRECT matched my named ranges properly.

  • @leonidasgoniotakis164
    @leonidasgoniotakis164 Місяць тому

    Thank you for this great and very-well explained tutorial! I have one question on 2:47 . In the data validation window , in "source" box you are inputting $H$4 and then the hash sign to reference the rest of the "horizontal array" , so my question is how does excel know that it needs to reference horizontally here ( as we want in this case of course) and not vertically (that it would be wrong for our goal) ? I hope my explanation makes sense , sorry if i missed something clarified here. Thank you in advance!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Місяць тому

      It simply references the array. It doesn't need to know it's horizontal or vertical. That information will be passed to it as part of the # operator.

  • @SianDoherty
    @SianDoherty 2 місяці тому +1

    Tasmania is also a region in Australia. Misses out yet again😭

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Yes, and most of the US states and UK counties. It wasn't personal, Tassie. Just a small dataset to illustrate an example 😉

  • @fankitty929
    @fankitty929 2 місяці тому

    Thanks for the video @MyOnlineTrainingHub, super helpful.
    I'm wondering if there's a way it can be dynamic in the sense that it'll only show regions that haven't been selected before as the input expands?
    Trying to use this as a rostering tool for a small team - as such we need to have the ability to only select staff that haven't been assigned on that day and time. (Dependent factors: location, time and day as some staff can't work certain days/hours)

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Yes: www.myonlinetraininghub.com/reducing-data-validation-list

  • @kevinbush4300
    @kevinbush4300 2 місяці тому

    Oooohh!!
    It's about time this game changed!!

  • @stefankirst3234
    @stefankirst3234 2 місяці тому

    Thanks for these straightforward concepts! However, I personally would´t use the Cell function because it tends to break if the workbook is shared between users with different language settings. Or is there a trick to avoid that?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      When using "address" in CELL it should be fine because it's only returning a cell reference.

    • @stefankirst3234
      @stefankirst3234 2 місяці тому

      @@MyOnlineTrainingHub the german version is =ZELLE("adresse") and while ZELLE will translate to CELL when an english user opens the workbook the parameter in doublequotes won´t translate properly causing the function to break

  • @mogarrett3045
    @mogarrett3045 2 місяці тому

    excellent

  • @tabithaowens348
    @tabithaowens348 2 місяці тому +1

    Will the second method work in a table formatting?

    • @michaelbrown8821
      @michaelbrown8821 2 місяці тому +3

      I've used the 2nd method in tables, and it works the same. A life saver.

    • @tabithaowens348
      @tabithaowens348 Місяць тому +1

      @@michaelbrown8821 sweet! Thank you! Can’t tell you how many hours I’ve spent figuring out streamlining data/lists lol 😂

  • @workstuff5253
    @workstuff5253 2 місяці тому

    I think they are the same, but, is there any advantage to using TRANSPOSE instead of TOROW?

  • @ahmetozlemis7694
    @ahmetozlemis7694 2 місяці тому

    I think method 2 is easily breakable, since h4 returns a list according to last cell that was edited. If i choose a country in first line and a region accoring to that, and repeat it for the second line, region dropdown menu for the first line will show the regions of the second line, because last country that was selected was in second line, not the first.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      The solution to that is to use the double-click variation for method 2.

  • @yabkhezr
    @yabkhezr 2 місяці тому

    great

  • @benbowyang
    @benbowyang 2 місяці тому +6

    The folks in Tasmania are not going to be happy!!!

  • @vida1719
    @vida1719 2 місяці тому

    All these methods are great. However, some users change values in the original list and then don’t understand why the chart still shows old values. The green triangles in the cells with drop-down lists indicate this issue. It would be easier if Excel automatically updated these values.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Sure, that would be easier, but it can't do that. You can modify the source for the first drop down to an IF formula that checks if the second drop down cell is empty. If it's not empty it only displays the text "clear region cell first". e.g. in the data validation source for the country drop down:
      =IF(C5="",$H$4#,$J$3)
      Where cell J3 contains the text "clear region cell first"

  • @huthmann
    @huthmann Місяць тому

    Wow!

  • @andrusmihail5660
    @andrusmihail5660 2 місяці тому

    Buna ziua,
    Te urmaresc mereu cu multa, multa placere!
    Desi am cunostinte limitate despre excel si am 67 de ani, inteleg APROAPE TOT. Nu ma las si vreau sa stiu mai mult, asta datorita tie. Te rog sa-mi recomanzi tutorialul in care descrii cum se scriu formulele imbricate, sau functie in functie.
    Cu recunostinta,
    Sorin

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Please see this tutorial on nested IF: www.myonlinetraininghub.com/excel-nested-ifs-explained

  • @mohammadj.shamim9342
    @mohammadj.shamim9342 2 місяці тому +2

    It is awesome, but It has one limitation. If someone clicks another cell, the filter gets erased.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Only until you select the country again, or in the case of the last example, double click.

  • @dbruce3254
    @dbruce3254 2 місяці тому +1

    AL=Alabama, AK=Alaska

  • @brighttriangle
    @brighttriangle 2 місяці тому

    At 2:04, you mention the "Array of Arrays limitation." I tried this workaround in H4:
    =BYCOL(H4#,LAMBDA(curVal,FILTER(TblRegions[Region],TblRegions[Country]=curVal,"")))
    Sadly, that did not work. It must be the same principle. Anyway, thank you for another great video.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому +1

      Glad you liked it! The array of arrays limit has no workaround 😁

  • @MarkSmith-bj2gf
    @MarkSmith-bj2gf 2 місяці тому

    Is there a way to have a drop down within a drop down?

  • @felixstraube2784
    @felixstraube2784 Місяць тому

    For the second method, lets say no country or state are selected yet. Then select a country in B5, and then a state in C5. Then I go to B6 select another country, but then I decide to change the state of the previous country C5... it will show the states of the country selected in B6, not B5 as it should, wouldn't it?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Місяць тому +1

      Yes, this is how conditional formatting has always worked, so I suggest adding some conditional formatting to highlight cells that become out of sync like this so you can easily see and fix them.

    • @felixstraube2784
      @felixstraube2784 Місяць тому

      I mean the list in the drop down will be the incorrect list.

    • @felixstraube2784
      @felixstraube2784 Місяць тому +1

      With these named ranges you don't neet helper cells for the dependent dropdown
      Countries
      =TblRegions[Country]
      Regions
      =INDEX(TblRegions[[Region]:[Region]], MATCH($B5, TblRegions[[Country]:[Country]], 0)):INDEX(TblRegions[[Region]:[Region]], MATCH(2,1/(TblRegions[[Country]:[Country]]=$B5)))
      City
      =INDEX(TblRegions[[City]:[City]], MATCH($B5&$C5, TblRegions[[Country]:[Country]]&TblRegions[[Region]:[Region]], 0)):INDEX(TblRegions[[City]:[City]], MATCH(2,1/(TblRegions[[Country]:[Country]]=$B5)/(TblRegions[[Region]:[Region]]=$C5)))

  • @wiernixie
    @wiernixie 2 місяці тому

    I get an error when accessing the page to download the practice file. This site can’t be reached
    The web page at might be temporarily down or it may have moved permanently to a new web address.
    ERR_FAILED

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      I just tested it and I don't get an error. Please try again with a different browser: bit.ly/ddlists24file

  • @njzzb
    @njzzb 2 місяці тому +1

    都不是非常完美,第一个相对实用,开发出来给别人用的时候。至少不出错。

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Yes, the first one is perfect especially if you don't expect to add more data to your table.

  • @vesaruusunen5685
    @vesaruusunen5685 2 місяці тому +1

    Great viedo. Thanks.
    One minor think. Sort should be done before unique. Excel works faster that way. (source: ua-cam.com/video/FDBHoDaIaUE/v-deo.html)
    In this example it doesn't matter, but to get it in to the flesh memory it would be better to do it allways the correct way. Like this: =transpose(unique(sort(TblRegions[Country)))

  • @isaacakande2537
    @isaacakande2537 26 днів тому

    Insightful as always.
    For some reason, my data validation is not able to accept formula =XLOOKUP(B5,$H$4:$P$4,$H$5:$P$5". Is there a reason why?
    there error message says "this entry leads to an error. Try entering different values to continue.
    would really appreciate a feedback Tracy. Thank you.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  26 днів тому

      Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

    • @isaacakande2537
      @isaacakande2537 26 днів тому

      @@MyOnlineTrainingHub alright
      Thank you

  • @SothearithKONGMrMuyKhmer
    @SothearithKONGMrMuyKhmer 2 місяці тому

  • @ahmedshalaby9343
    @ahmedshalaby9343 2 місяці тому

    what's # ?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 місяці тому

      Explained here: www.myonlinetraininghub.com/hash-sign-in-excel-formulas