Easiest Multi-Level Drop-Down Lists in Excel!

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

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

  • @MethiniBongchomphoMKTP.Mariya
    @MethiniBongchomphoMKTP.Mariya 10 ชั่วโมงที่ผ่านมา +1

    Thank you so much. I search a lot of method how to do that. This video very useful.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  3 ชั่วโมงที่ผ่านมา

      Great news, glad I could help. 😁

  • @warrennalty6599
    @warrennalty6599 22 วันที่ผ่านมา +11

    I found it easier to use unique(sort(filter() with the filter referring to the higher level selection.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +3

      I believe UNIQUE/SORT/FILTER only works if you output the result to a range. Therefore, it's only suitable with a single chain of dependent lists.
      If you have multiple rows of cell requiring drop-downs, I don't think it works so well.

  • @stevereed5776
    @stevereed5776 21 วันที่ผ่านมา +3

    Very nice Mark. The problem with these solutions is that if you choose a different shoe, it would be nice to blank out the other two cells. I still like this solution though.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  19 วันที่ผ่านมา +1

      By design, a hardcoded value in one cell cannot change a hardcoded value in another cell - so it's unlikely that will ever be possible. Unless of course you sprinkle in a bit of VBA to achieve it for you.

  • @williamarthur4801
    @williamarthur4801 17 วันที่ผ่านมา

    I have done dependent drop downs but they always had helpers and were a bit fiddley, really great use of these newer functions.

  • @sledgehammer-productions
    @sledgehammer-productions 21 วันที่ผ่านมา +1

    As I didn't have DROP and CHOOSECOL at the time (about a year ago), I've managed to make 4 level dependent dropdown, with some helper cells and filter arrays. It could be 5, I don't remember, would have to look at the file, but it was a nice challenge. Not sure anyone using the model really appreciates it, but hell, that's the live of a Excel model builder 😄

  • @ximox
    @ximox 18 วันที่ผ่านมา +2

    Very useful, I used to use "indrect" formula. This look interesting too.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  18 วันที่ผ่านมา +1

      As I’m sure you’ve experienced, INDIRECT is a pain to manage. The method in this video, takes a bit more skill, but is significantly quicker to setup and maintain.

  • @IvanCortinas_ES
    @IvanCortinas_ES 21 วันที่ผ่านมา +1

    Excellent suggestion, Mark. I like it because the INDIRECT function does not appear. Thanks for sharing.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +2

      It’s much easier to manage than INDIRECT because we don’t have to add new named ranges for each new item.

  • @cmbocc
    @cmbocc 21 วันที่ผ่านมา +1

    I agree that’s one of best and easiest methods to use, and well explained. Thanks Mark!

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา

      Thanks - I hope you can put it to good use. 👍

  • @McIlravyInc
    @McIlravyInc 20 วันที่ผ่านมา

    I almost understood why this works! Ha! It does make more sense as a solution than the other methods I've seen.
    I'm looking forward to part 2 where we can choose any one of the categories and the others bwcome dependent... because sometimes I need a certain color shoe vs a certain style! 😊

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  19 วันที่ผ่านมา

      Part 2 would be very difficult - maybe impossible. You would need to create different sorted areas for every possible order. Then use an IF inside the Data Validation to select the relevant area depending on which other options are selected.
      Maybe something for the membership - probably too much for YT.

  • @pkrempf
    @pkrempf 22 วันที่ผ่านมา +2

    Thank you! This is an excellent video showcasing a range of new features. However, I'd like to highlight one point: when I tried the formula with # in Data Validation today in Excel 365, I couldn't validate it. After updating my Excel 365, I was able to validate the formula in Data Validation. So, while the process is fantastic, I’ll hold off on distributing data validation with # references until it’s more stable across versions.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +2

      The issue is not the #, which is available as far back as Excel 2021. The issue is DROP and TAKE functions, which are in Excel 365 and Excel 2024.
      With such a large percentage of users on Excel 365, I see less and less reason to state which versions things work on.

  • @adhamm5503
    @adhamm5503 21 วันที่ผ่านมา +2

    Simply genius 👏 Thanks bro.
    I was thinking what if we select shoe type from first list and below it displays a picture of the type of toe and how much qty available and same for colors.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  20 วันที่ผ่านมา

      Sounds like a beautiful solution to me. Great idea.

  • @PaulEBrownbill
    @PaulEBrownbill 21 วันที่ผ่านมา +1

    I sort of followed that through, what a formula you end up with, thanks Paul

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +1

      If you’ve not see these formulas before, it can be a little tricky.

  • @omarnader776
    @omarnader776 21 วันที่ผ่านมา +1

    again and again, thank you so much for your easier and useful explanation

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +1

      You’re welcome - I glad you found it useful.

  • @Hortster
    @Hortster 22 วันที่ผ่านมา +1

    Interesting technique, I'll have to compare it to the indirect function I'm using to see if it's more efficient. Thanks for sharing.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา

      With INDIRECT you have to create separate ranges for every items and for every combination of items.
      With this method you don't have to do that. So it should be significantly easier to keep up-to-date.

  • @RichardJones73
    @RichardJones73 22 วันที่ผ่านมา +1

    Wow. Excellently explained as usual. I wonder how performance will be affected if using large data sets though

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +1

      A data validation list only calculates when the button is clicked, it's not part of the main calculation chain. So I believe performance impact should be insignificant.

  • @kebincui
    @kebincui 22 วันที่ผ่านมา +1

    Very creative. Excellent as always. Thanks Mark👍

  • @anillpatel
    @anillpatel 22 วันที่ผ่านมา +1

    Excellent video presentation.
    Very useful.
    Thanks Mark.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา

      Thank you. I'm glad you enjoyed it 😁

  • @hollydunne2687
    @hollydunne2687 4 วันที่ผ่านมา

    Fantastic video! Definitely helped me start understanding more about how the dynamic arrays work and really useful practical application. I was wondering if there's any difference in omitting the "-" part of the XMATCH arguments and just putting the ampersand? ie K6&L6,$F$6#&$G$6#

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  3 วันที่ผ่านมา +1

      If your lookup array contains the following:
      A1 | 1
      A | 11
      The only way to select the correct value is by using a space character between the values (which is what the "-" does). So, it's a good idea to always include one, just in case.

    • @hollydunne2687
      @hollydunne2687 2 วันที่ผ่านมา

      @ExcelOffTheGrid thanks very much!

  • @iincitr
    @iincitr 19 วันที่ผ่านมา

    Thank you for your very good explanation. I like it.

  • @chta2010
    @chta2010 19 วันที่ผ่านมา

    Hi Mark! Your tips are great; I bet, you're also dreaming in rows and columns ;-)

  • @alexrosen8762
    @alexrosen8762 21 วันที่ผ่านมา +1

    Great stuff 👌

  • @chrism9037
    @chrism9037 22 วันที่ผ่านมา +1

    Awesome, thanks Mark

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา

      Thanks Chris, My pleasure!

  • @carolynbauman6670
    @carolynbauman6670 22 วันที่ผ่านมา +1

    Question: To select the color you would have to have already selected the toe? If I wanted to select the color second after the shoe, would I need to redo the order of the dependent formulas?

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  21 วันที่ผ่านมา +1

      You could have any order you want - you just need to understand which formula to use for the
      - Drop-down
      - Dependent drop-down
      - Dependent-dependent drop-down
      You can then keep adding to the formulas and create as many levels as you wish.

  • @354dw
    @354dw 7 วันที่ผ่านมา

    Great technique. The next step is to reset the dependant list selection if the parent is changed….

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  7 วันที่ผ่านมา +1

      A cell cannot change the existence of a hardcode value in another cell, so you would need VBA for.
      You can always use conditional formatting to highlight the items which are wrong.

  • @KO1967
    @KO1967 9 วันที่ผ่านมา

    There are always ways in Excel to do things creatively as you have discovered. It's just too bad that Excel can't allow the FILTER function within a named range formula.

  • @hugocristini9433
    @hugocristini9433 15 วันที่ผ่านมา +1

    "No tricky formula" : proceeds to use formulas I've never heard about with array brackets hahaha
    The solution was fairly easy to apply though !! Many thanks !!

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  13 วันที่ผ่านมา +1

      You're the first person to identify that I said "No tricky formulas" and then proceeded to pull out some reasonably tricky formulas. 10/10 for paying attention. 😁

  • @DimasFajar-ns4vb
    @DimasFajar-ns4vb 22 วันที่ผ่านมา

    wow and peace be upon you sir from me

  • @panoskatotriotis
    @panoskatotriotis 15 วันที่ผ่านมา

    Hi is there a way to user the filter function results on a table?

  • @LauraGonzalez-jo8wh
    @LauraGonzalez-jo8wh 20 วันที่ผ่านมา +1

    Excellent Mark, I used =SORTBY(Data,Data[Shoe]) to sort the table, the other part was amazing.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  20 วันที่ผ่านมา

      You need a multi level sort to ensure the ranges for the sub levels work. So you need to sort in the Shoe, Toe, then Color.
      You can achieve this with SORTBY, but the SORT syntax is shorter.
      If your data is already grouped correctly, you don’t need to sort at all.

    • @LauraGonzalez-jo8wh
      @LauraGonzalez-jo8wh 18 วันที่ผ่านมา +1

      @@ExcelOffTheGrid, =SORTBY(Data,Data[Shoe],1,Data[Toe],1,Data[Color],1) 😃

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  16 วันที่ผ่านมา +2

      Yes, that will do it.

  • @AdamMarks
    @AdamMarks 10 วันที่ผ่านมา

    When I do the Data Validations, it doesn't give me the unique listing you have, but instead duplicates all the values in the range if the are multiple instances of the value. Any idea why that is?

  • @FrancoisBothaZA
    @FrancoisBothaZA 17 วันที่ผ่านมา

    Surely FILTER() is much simpler and achieves the same for the dependent lists, or am I missing something?

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  17 วันที่ผ่านมา +1

      How are you going to use FILTER,
      if you have 3 columns of dependent drop-downs across 100 rows?

  • @ozgur937
    @ozgur937 21 วันที่ผ่านมา

    Thanks, I remember there was a total brainf.k way for depended dropdown lists.

  • @Al-Ahdal
    @Al-Ahdal 22 วันที่ผ่านมา +1

    1st comment