+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast
Results 1 to 15 of 47

Index and dependent dropdown list

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

    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
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    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
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,870

    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!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

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

    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
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    Re: Index and dependent dropdown list

    Please add sample expected results

  6. #6
    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
    17,870

    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 2016
    Posts
    106

    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 nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    6,367

    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
    Click *Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) If you are satisfied and add reputation the for the one who helps you

    Regards

    Samba

  9. #9
    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
    17,870

    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 nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    6,367

    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
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,870

    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 2016
    Posts
    106

    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
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,870

    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 2016
    Posts
    106

    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 2016
    Posts
    106

    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.

+ Reply to Thread
Page 1 of 4 1 2 3 ... 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