Dependent Drop Down List

  • I am attempting to create a dependent drop down list. I have 2 named ranges in the attached file created with this formula.


    List1=OFFSET(Sheet1!$A$2,0,0,(COUNTBLANK(Sheet1!$A:$A)+COUNTA(Sheet1!$A:$A)-1),1)

    List2=OFFSET(Sheet1!$B$2,0,0,(COUNTBLANK(Sheet1!$B:$B)+COUNTA(Sheet1!$B:$B)-1),1)


    I have a drop down list in Cell B4 of Sheet2 consisting of unique values in Column A of Sheet1. I am attempting to create a dependent drop down list of Column B values in Sheet1 in cell E4 of Sheet2. The formula I have is as follows.


    =IFERROR(INDEX(List2,SMALL(IF($B$4=List1,ROW(List2)-ROW(Sheet1!$A$2)+1,ROW(1:1))),"")


    However, I am not able to create the dependent list. Any suggestions on how to resolve this?


    Testing1.xlsx

  • My method involves using an Excel Table for the lists. Tables are dynamic so you don't need complicated formulas to create the Named Ranges.


    If you need help applying this method post back.

  • I have attached a workbook modeled on my data. In Sheet1, Column A has the contents of my 1st dropdown list, Column B has the contents of my dependent dropdown list.


    In Sheet2, I have placed the 1st dropdown list in Cell B4, I would like to create the dependent dropdown list in Cell E4.


    My final objective is to dynamically output the contents of Col C in Sheet 1 based on the selections in the 2 dropdown lists.


    Testing2.xlsx

  • You are using MergedCells which most of the time creates problems. I doubt if you can create any Data Validation list from ells which are merged.


    SORT and UNIQUE are only available in Office365, so will not work if you intend sharing with users that don't have a subscription.


    You are trying to use a formula for the source of the list which will not work and I cannot see what you want in the second list

  • I managed to create the dependent drop down list by applying the following formula in Cell F2 of Sheet1.


    =SORT(UNIQUE(FILTER(List2,(List2<>"")*(List1=Sheet2!$B$4))))


    This creates a unique list of "Dependent List" values filtered based on the selection of the 1st dropdown list.


    and then refer to these values by applying the following formula for the validation list in cell E4 of Sheet2.


    =Sheet1!$F$2#.


    I would now like to output the contents of Col C in Sheet1 to Cell B8 of Sheet2. I typed the following formula in cell G2 of Sheet1 to get a unique list of "Final Output" values.


    =UNIQUE(FILTER(List3,(List3<>"")*(List2=Sheet2!$E$4))).


    However, this formula does not work with merged cells in "Dependent Values". Could you suggest a suitable formula that would work with merged cells?


    The new workbook is attached below.


    Testing2.xlsm

  • Hello,


    For your dependent cascading drop down lists, you should completely forget your merged cells


    Attached is your test file with a VBA solution


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the file ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)