+ Reply to Thread
Results 1 to 47 of 47

Index and dependent dropdown list

  1. #1
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Index and dependent dropdown list

    Hi guys, I started a thread with similar request. The thread is closed but I need to make additional changes. Here is a link to the original request.
    https://www.excelforum.com/excel-for...-criteria.html
    John and Siva helped me with first thread, so big thanks to them.
    I also posted something similar on another forum but I didn’t get answer, so I am back here and I hope you guys help me out.
    So, here we go:
    I have a table of products and equipment. Each cell where a product is made on a given equipment is marked “Y” or “‡”. I need your help with 3 formulas:
    In column FS: find the name of the product with highest MDD (row 12) and lowest batch size (row 10)
    In column FT: how many products are made on a given equipment (excluding duplicate names in E3:FQ3)
    I tried COUNTIF(F13:FQ13,"Y")but it is counting everything including duplicate product names. I have duplicate product names because they are made in two sizes.
    In the Calculations sheet, I need to create a drop down list dependent on the equipment list in B1 list. So if I select equipment from the first list, the second list should show only those products made on the selected equipment (Again excluding duplicate names).

    The worksheet is attached.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    Add link to other forum in case you get a reply there.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    To follow up what John has said: you have had the cross-posting rules pointed out to you and you said (to me, in your other thread) that you understood them. I am, therefore, rather taken aback to find another thread that does not contain the required cross-post LINK. It is not enough just to say that you have asked the question elsewhere. I would also remind you that you are required to do the same thing on all forums, not just this one. Please provide the missing link forthwith. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Quote Originally Posted by JohnTopley View Post
    Add link to other forum in case you get a reply there.
    Sure John, I will, if I get a reply.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    Please add sample expected results

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Sure John, I will, if I get a reply.
    No, you will provide the link regardless. If you don't, this thread will be closed.
    Last edited by AliGW; 01-03-2018 at 01:26 PM.

  7. #7
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    AliGW, do I still need to provide a link to another thread on another forum that has no reply!! I don't fathom how posting empty thread would help anyone? But if you insist, I can definitely do that.
    Last edited by AliGW; 01-03-2018 at 01:35 PM. Reason: Unnecessary quotation removed.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index and dependent dropdown list

    ft13
    Please Login or Register  to view this content.
    Try this array formula (Confirm with Shift +Ctrl+Enter) and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Yes, because there could be a reply there at any time, and that is what our rules require you to do. Look - it's a simple enough rule: if you have asked the same thing anywhere else, you supply a link to it in your opening post. It's really not hard to do!

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index and dependent dropdown list

    @ AliGW
    By seeing your message I tried to delete my post, before that you deleted my post

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Siva - I will reinstate it once the link has been provided. Sorry for the inconvenience to you.

  12. #12
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Ok here is the link

    https://chandoo.org/forum/threads/in...riteria.36836/

    I was trying to follow your logic when you state on the other thread that you don't want to waste people's time, so I figured how linking a thread that doesn't answer the request, would help other people. To me, that's common sense.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Thank you.

    ... so I figured how linking a thread that doesn't answer the request, would help other people ...
    Just to clarify: it's not for the benefit of others looking for help, it's for the benefit of those trying to help you so they can keep an eye on the other thread and see if anything has been suggested. This prevents unnecessary repetition and means that helpers here do not subsequently discover that they have wasted their time.

    It is also expected that you tell us if you have received a solution elsewhere so that your issue can be marked solved here and everybody knows where the solution can be found.
    Last edited by AliGW; 01-03-2018 at 01:56 PM.

  14. #14
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Quote Originally Posted by JohnTopley View Post
    Please add sample expected results
    In row 54, there are two products (actually one with 2 sizes) with the highest MDD of 1200, however, the one with the smaller size should be the product, so in FS54 Nurona 400 mg Capsules should appear.

    here is a screenshot

    products.png

    I also tried to modify Siva's formula in FR13 to fit the new criteria but I messed it up.
    IF(COUNTIF(E44:FQ44,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MAX(($E44:$FQ44="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000))),INDEX(($E44:$FQ44="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000),0),0)),"")
    Last edited by mq1973; 01-03-2018 at 02:25 PM.

  15. #15
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Quote Originally Posted by AliGW View Post
    it's for the benefit of those trying to help you so they can keep an eye on the other thread and see if anything has been suggested. This prevents unnecessary repetition and means that helpers here do not subsequently discover that they have wasted their time.
    Point taken.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    try

    in FS13

    =IF($FR13="","",SUMPRODUCT(($E$8:$FQ$8)*($E$4:$FQ$4=INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))*(ISNUMBER(SEARCH("s",$E$7:$FQ$7)))))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    in FT13

    =SUM(IF(FREQUENCY(IF($E13:$FQ13<>"", MATCH($E$4:$FQ$4,$E$4:$FQ$4,0)),COLUMN($E13:$FQ13)-COLUMN($E13)+1),1))


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Thanks John, I really appreciate it. The formula is FT13 works as intended, but the formula in FS is not.
    FS13 needs to show the product name that meets the criteria (max MDD and lowest batch size).

    This is the formula I was trying to modify to meet these criteria:
    IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MAX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000))),INDEX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000),0),0)),"")

    John.png

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    In Fs13

    =IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  19. #19
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    John, thank you sooooo much. It works now as intended
    2 formulas of 3 down.

    I don't if you would also help me with the dependent drop down list.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    I answered that in your previous post where I provided a drop down list of equipment and product list.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Yes John, but you created a drop down menu in A3 . Columns B and G are lists, not dropdown menus and that's exactly what I needed in that sheet. I am trying to create on another sheet two drop down menu, where if I select an equipment from the first dropdown menu, the second drop down menu should list only those products (excluding duplicate product names) that are made on the selected equipment in the first drop down menu. It needs to be a drop down menu, so I can add additional calculations.
    dropdown.png

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    TRy

    in B2

    =IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH($B$1,Equipment_List,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),"")

    in C2


    =IFERROR(INDEX(Trains!$E$7:$FQ$7,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH($B$1,Equipment_List,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),"")

    BOTH ...
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    there are duplicates as it does not differentiate on size

    If you want a non-unique list you will have to derive it from list in column B.
    Last edited by JohnTopley; 01-03-2018 at 04:43 PM.

  23. #23
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    John, both formulas result in blanks.
    I just need help in the second dropdown menu. I made the first drop down menu on another sheet in B1. I need the second drop down menu in E1.

    dropdown2.png
    Last edited by mq1973; 01-03-2018 at 04:54 PM.

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    If you put the formula in B2 as your posted file you will get a list , after entering with CSE in B2 and then copying formula down.

  25. #25
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    I put the first formula in B2 but still getting blank.

    John, please keep in mind that my knowledge of Excel formulas is limited to basic functions and I am trying to learn more complicated formulas.

    dropdown3.png

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Copy and paste the formula into B2. Instead of pressing ENTER to exit the cell, hold down the CONTROL and SHIFT keys together, and whilst holding them, click ENTER. Then you can drag copy the formula down.

  27. #27
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Thank AliGW, I did that exactly but I am still getting blank. I am not sure if I am missing something.

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Have curly brackets appeared around the formula? If you have entered it correctly, they should have. You do not type these yourself.

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Also can you check that there isn’t a stray space at the beginning or end of the formula?

  30. #30
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Yes, curly brackets appeared around the formula and there are no spaces at the beginning or end.

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Attach the workbook here.

  32. #32
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Here is the file. For your information, I removed the formula because it didn't work.
    If you look at the calculation sheet, you will see where I intend to create the 2 dependent dropdown menus.
    Attached Files Attached Files
    Last edited by mq1973; 01-03-2018 at 05:46 PM.

  33. #33
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    See attached : Sheet "Calculations"

    in D2

    =IFERROR(INDEX(Products,MATCH(SUM(COUNTIF(D$1:D1,Products)),COUNTIF(Products,"<"&Products),0)),"")


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    "Products" is named range of data in b2 onward

    "Unique_Product_list" is named range of column D

    in E2

    Data Validation

    Allow: =List

    Source:

    =Unique_Product_LIst
    Attached Files Attached Files

  34. #34
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    John, two comments.

    1. I randomly selected an equipment where are 5 products are made, so the dropdown menu in E1 should show 5 products. In this case, it's showing the entire product list.
    2. I need to show in the Calculations two dropdowns only and nothing else because I am going to create more fields below the dropdown menus. So the product list that appears on B2, C2, and D2 onwards, will prevent from creation additional calculation fields below the dropdowns menus.

    I hope I am not troubling you. I do appreciate your efforts.


    dropdown4.png

  35. #35
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    This in B2

    =IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH($B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($F$4)+1,""),ROWS($B$2:$B2))),"")


    "Equipment_Name" is range B13:B65 in "Trains

    "...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

  36. #36
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Hi John and sorry for the late response because we are having a snow storm right now.
    I was verifying the formula. I am noticing that second drop down menu is working for some equipment but not all of them.
    See for example this. The circled equipment has 7 unique values (products)


    dropdownnew.png

    But the formula is not showing all products exactly. the product in FQ4 is missing (ART 50mg capsules)

    dropdownnew2.png

    I do have a suggestion though! Can I merge the cells in the reference sheet to remove the duplicate products, without messing up other fomrmulas?

  37. #37
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    Typo

    in B2

    =IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH($B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),"")

    Do NOT merge cells: very bad practice.

  38. #38
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Thanks so much John! It works perfect

    I need one more favor from you. I need to show only cells B1 and E1 and not have anything else on the that tab. My goal is create fields below B1 and E1. So I copied B1 and E1 onto a new sheet and then E1 stopped working.

    fixed drop down menu.jpg

    My last question to you is regarding your formula in FS13
    IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))
    This formula is supposed to find products based on 2 criteria, highest MMD (E$12:$FQ$12) and lowest batch size (E$8:$FQ$8). I don't see E$8:$FQ$8 in the formula!
    Last edited by mq1973; 01-05-2018 at 12:40 AM.

  39. #39
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    Removed by JT until forum links requested are provided.
    Last edited by AliGW; 01-05-2018 at 08:42 AM.

  40. #40
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and dependent dropdown list

    Please be aware that the OP has opened yet another thread about this on the other forum, to which no link has yet been provided here, despite the rules having been made very clear earlier in this thread.

    No further help here, please, until that link has been provided.

  41. #41
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    I created my thread with intention to get help with my request and not waste anyone's time or effort. Props to whoever helped or tried to help me. Adding a reference to another thread that didn't answer the original request doesn't add any value. Plus, once the thread is not on the top threads list, chances of it to go unnoticed/ ignored are high. That's my two cents.

    Anyway, for the purpose of complying with the forum rules, here is the link to the other thread https://chandoo.org/forum/threads/he...formula.36870/

  42. #42
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    For FS13 try

    =IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(($E$8:$FQ$8)*($E$4:$FQ$4=INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))*(ISNUMBER(SEARCH("s",$E$7:$FQ$7)))),$E$8:$FQ$8,0)))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Re B1 and E1

    you can delete Column C

    for the other two lists: you can put the formula in other columns and hide the columns OR place them in another sheet but use the same named range names.

  43. #43
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Thanks John,

    I created a new sheet (CalculationsNew) and created 2 dropdown menus in B1 (name range: Equipment_List) and E1 (name range: Unique_Product_List) but now E1 is stuck on one list and doesn't change according to B1. Can you please have a look at the attached file.

    new calculations sheet.jpg

    Regarding the new formula in FS 13 IF($FR16="","",INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(($E$8:$FQ$8)*($E$4:$FQ$4=INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E16:$FQ16="y")),($E$12:$FQ$12)*($E16:$FQ16="y"),0)))*(ISNUMBER(SEARCH("s",$E$7:$FQ$7)))),$E$8:$FQ$8,0))), it is showing a product that is not made on equipment row 16


    FS13.jpg

  44. #44
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    Try

    =IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))

    in B2 of "Calculations"

    =IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH(CalculationsNew!$B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),"")


    My last one too!

  45. #45
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    FS13 finally is perfect, thank you so much


    I have no idea what happens to the B2 formula but as soon as I changed to this
    IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH(CalculationsNew!$B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),""), it stopped working properly.

    I totally understand when you say this is your last one too and I really thank you for helping me with this. I just wish if the E2 formula worked as well.
    Last edited by mq1973; 01-07-2018 at 04:04 AM.

  46. #46
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and dependent dropdown list

    See attached: I "tidied" up the "Calculations" sheet so it only list the "Products" and "Unique Products" based on B1 in "CalculationsNew".

    So you only select from B1 in "Calculationsnew" and get the list in E1.

    It was always working so I don't understand your reference to E2

  47. #47
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2021
    Posts
    211

    Re: Index and dependent dropdown list

    Wow. It is working perfect now
    I am sorry, my reference to E2 was a typo. What I meant was B2 in the Calculations sheet.

    Thank so you much John, I really appreciate your help and patience with me.

    What puzzled me is that when I did the same thing before your last post, I could not get the CalculationsNew to work properly unless I messed something, but it is working perfect now.
    Last edited by mq1973; 01-07-2018 at 11:35 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dependent dropdown list
    By Spikyd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2017, 08:09 AM
  2. [SOLVED] Dependent Dropdown-List problem
    By Challebjoern in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2017, 11:45 AM
  3. Replies: 7
    Last Post: 11-21-2016, 04:40 PM
  4. [SOLVED] Multi dependent dropdown list
    By akhileshgs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2014, 10:24 PM
  5. [SOLVED] Dependent Dropdown List Using Index and Small Won't Return Multiple Values
    By ebevis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2013, 02:28 AM
  6. [SOLVED] How to create third dependent dropdown list?
    By kyawzw in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-26-2012, 02:05 AM
  7. Dependent dropdown from a sorted list.
    By flogmat in forum Excel General
    Replies: 3
    Last Post: 09-04-2010, 04:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1