Excel – Create a dependent drop down list in a single cell in Excel

microsoft excelmicrosoft-excel-2010

I am trying to create a dependent drop down list for a High School. The User will select cell A1, Click on Hallway 3(for example), then the user will click on cell A1 again and then select the Room #, 325 (for example). The final result should be that cell A1 shows 325. I can create the first drop down list, but the second part is where I am having great trouble. I am using Excel 2010. Any help would be appreciated. Thanks

Best Answer

I doubt that it is possible to do that in the same cell (A1 for the first item and than again on the same cell for the second item). I had a similar problem myself and this is what I used to resolve it. Now I have several drop down menus dependent of each other, in the first sheet of excel workbook that some of my colleagues are using to "fill out the form" :) Here is also good example on how to resolve this problem.

Example table which could be something you need

A           B
HALLWAY     ROOM
Hallway 3   353
Hallway 3   325
Hallway 1   157
Hallway 1   124
Hallway 2   234
Hallway 2   265

Create two drop down lists.

First drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from column B, based on chosen value in the first drop down list.

Create a dynamic named range on Formulas > Name Manager > New > name it hallway > put =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000)) in "Refers to:" field > Close

Create a unique distinct list from column A. Select Sheet2 > select A2 > type "=INDEX(hallway,MATCH(0,COUNTIF($A$1:A1,hallway),0))" + CTRL + SHIFT + ENTER > Copy cell A2 and paste it down as far as needed.

Create a dynamic named range to get unique distinct list on Formulas > Name Manager > New > name it uniqhall > put =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$2:$A$1000="", "", 1)), 1) in "Refers to:" field > Close

Create drop down list > Select Sheet1 > Select cell D2 > Click on Data > Data validation button > Data Validation > Allow: List > Source: =uniqhall > Ok

Secondary unique list based on only one chosen cell value in first drop down list.

Create a dynamic named range on Formulas > Name Manager > New > name it room > put =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) in "Refers to:" field > Close

Create a unique distinct list from column B. Select Sheet2 > select B2 > type "=INDEX(room, MATCH(0, COUNTIF($B$1:B1, room)+(order<>Sheet1!$D$2), 0))" + CTRL + SHIFT + ENTER > Copy cell B2 and paste it down as far as needed.

Create a dynamic named range to get unique distinct list on Formulas > Name Manager > New > name it uniqroom > put =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$2:$B$1000="", "", 1)), 1) in "Refers to:" field > Close

Create drop down list > Select Sheet1 > Select cell D5 > Clock on Data > Data validation button > Data validation > Allow: List > Source: =uniqroom > Ok

Hope this solves your problem up to some point :)

Related Question