+ Reply to Thread
Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 31 to 45 of 47

Index and dependent dropdown list

  1. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    21,379

    Re: Index and dependent dropdown list

    Attach the workbook here.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  2. #32
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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.

  3. #33
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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

  4. #34
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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

  5. #35
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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

  6. #36
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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?

  7. #37
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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.

  8. #38
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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.

  9. #39
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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.

  10. #40
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    21,379

    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.

  11. #41
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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/

  12. #42
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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.

  13. #43
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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

  14. #44
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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!

  15. #45
    Forum Contributor
    Join Date
    04-26-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    106

    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.

+ Reply to Thread
Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Thread Information

Users Browsing this Thread

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

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