+ Reply to Thread
Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 16 to 30 of 47

Index and dependent dropdown list

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

    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

  2. #17
    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 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

  3. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    14,503

    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.

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

    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.

  5. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    14,503

    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

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

    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

  7. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    14,503

    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.

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

    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.

  9. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    14,503

    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.

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

    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

  11. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,320

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

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

    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.

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

    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.

  14. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,320

    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?

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

    Re: Index and dependent dropdown list

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

+ Reply to Thread
Page 2 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