+ Reply to Thread
Results 1 to 12 of 12

Multiple dependant drop downs

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    SS
    MS-Off Ver
    Excel 2007
    Posts
    29

    Multiple dependant drop downs

    Hello everyone and thanks in advance for your help. This is my first post and I will really apreciate a hand.

    Im breaking my head trying to do 3 dependant drop down menus. When you select the first one a list of options will apear on the second one and after you select the second one, a list will update on the third.

    I'm attaching a file to make my self clear.

    thanks in advance for your help
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Multiple dependant drop downs

    Hi Paconovellino,

    Welcome to the forum.

    See the attached file and where I have used dynamic name with Indirect function to solve your query. thanks.
    list.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    05-02-2012
    Location
    SS
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Multiple dependant drop downs

    Thanks, pretty impresive. One more question, is there a way to make "Student's Name" a dynamic list. Also, how can I make the lists not to show diplicate records.

    Thanks and regards.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple dependant drop downs

    Deleted... didn't count for students....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Multiple dependant drop downs

    You are welcome paconovellino..

    Student's name is the outcome of the selections under principal's name and Teacher's name... since both these are repeating many times but having different student name on each occurrence. for example B2: C20 has same Principal name and teacher name but different student's name..

    You need to work on your data first.. and then accordingly validation can be corrected.
    Like you have given =$C$2:$C$1048576 as the reference for principal's name but since they are only two, you can give the reference to C40:C41 or may be you can copy them in I1:I2 and give the reference of this.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple dependant drop downs

    Another attempt

    See if attached can help...

    Firstly, make a list of the unique principles in a row, e.g. in T1:U1 and under each principal enter the unique respectively assigned teachers.

    Create

    Then Data Validation for J3 would be to Allow: List and use source formula: =$T$:$U$1

    Then Data Validation for J5 would be to Allow: List and use source formula:
    Please Login or Register  to view this content.
    Then Data Validation for M3:

    Please Login or Register  to view this content.
    You would need to adjust the ranges, etc, if more principals and teachers added.

    Then in J8, summing formula would be:

    =IF(OR($J$3="",$J$5="",$M$3=""),"",SUMIFS(INDEX($D:$G,0,ROWS($J$8:$J8)),C:C,$J$3,B:B,$J$5,A:A,$M$3))

    copied down.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-02-2012
    Location
    SS
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Multiple dependant drop downs

    Thanks NBVC but I got lost on the Studen's name valiation. The drop down list its not showing but I guess if I use index and match it can work.

    Thanks for your help dilipandey now I undertood what you were saying about working on the data.

  8. #8
    Registered User
    Join Date
    05-02-2012
    Location
    SS
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Multiple dependant drop downs

    Im using Excel 2007, could that be why the data validation for M3 is not working?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple dependant drop downs

    Quote Originally Posted by paconovellino View Post
    Thanks NBVC but I got lost on the Studen's name valiation. The drop down list its not showing but I guess if I use index and match it can work.
    Is it in the attached? Perhaps I uploaded last time before saving that change....
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple dependant drop downs

    Ok, I see what's going on.... after you save and reopen, Student drop down doesn't work...

    Try this:

    Go to Formulas tab, Define Name, enter Studentsin Name field and formula:
    Please Login or Register  to view this content.
    in Refers to field and click Ok.

    Change Data Validation formula for M3 to simply: =Students
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-02-2012
    Location
    SS
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Multiple dependant drop downs

    Thanks for the help NBVC, I'm traying to give you more stars but I can

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple dependant drop downs

    No Worries.. the thanks here is enough

+ 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