+ Reply to Thread
Results 1 to 14 of 14

3 level matrix

  1. #1
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    3 level matrix

    Hello everybody,
    I have a problem to finish my exercise, there are three lists (listA, listB, listC) and three cells (H1), (L1), (M1).
    A B C
    1 listA listB listC
    2 40 40 40
    3 45 45 60
    4 50 40 30
    5 50 50 70
    6 50 50 120
    7 60 40 30
    8 60 40 40
    If I select cell H1=60 (belong to listA) , L1=40 (belong to listB), M1 should be a dropdown list that returns two values:40 and 30 (belong to listC)
    what i have to type on the Source of dropdown list?
    thank you very much in advance

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: 3 level matrix

    I think you get better help if you post an Excel file, without confidentional information.

    Please also add the desired result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3 level matrix

    Thank you oeldere
    in the excel file I have to associate a drop down list to cell (L2) "PickListC" once are selected the values in "PickListA" and "PickListB"
    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: 3 level matrix

    Please add in your sheet the desired result, and please also add the depending cells for the result.

    I looked ad your file, but i miss the relationship between A / B and C.

    P.s. is there also an relationship with column A (Type)?

  5. #5
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: 3 level matrix

    A possible solution using extra formulas to generate depending dropdown lists.
    Attached Files Attached Files
    Kind regards,
    Piet Bom

  6. #6
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3 level matrix

    Thank you oeldere
    If you take a look to excel file and select the drop downlist in H2 and J2 you should obtain corrisponding values to ListA and ListB respectively, so I have to create a dropdown list in L2 that returns 1 or more values on the ListC.
    Example:
    if I choose for H2=60 and J2=40 L2= should return in dropdown list 30 and 40 (exactly how B7=60, C7=40, D7=30 D8=40)
    Please accept my Apologize if i'm not so clear, my language is not English!!
    thank you

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,525

    Re: 3 level matrix

    Here's a vba solution.
    Try the attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3 level matrix

    Hi PietBom
    I appreciate a lot all the help you've given me,
    thank you very much your file works great!!!
    thanks

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: 3 level matrix

    maybe you can use an filter.

    see the attached file.

    my language is not English!! either
    my language is not Italian!! either
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3 level matrix

    Hi Great Jindon
    Your file is amazing and optimized
    thank you very much for your help

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,525

    Re: 3 level matrix

    Here's "Optimized" style of validation list. (all unique)

    Try
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3 level matrix

    Hi Oelder thank you for your file and your help
    my language is not English!!
    your English is much better than mine!!

  13. #13
    Registered User
    Join Date
    11-11-2012
    Location
    Rome
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3 level matrix

    I want express my vivid thanks for your quick support, guys!!!
    thank you so much

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: 3 level matrix

    Last option, this method creates the Drop Down ListB range based on the value chosen H1. Click on I1, then open the CTR-F3 Name Wizard to review the dynamic formula.

    Then click on J1, then open the CTRL-F3 Name Wizard and review the ListC dynamic formula. Now add ListC to the cell J1 as a drop down and you're good to go, no VBA or helper columns needed, just a couple of dynamic named ranges.
    Attached Files Attached Files
    Last edited by JBeaucaire; 11-12-2012 at 01:44 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

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