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
Excel – Create a dependent drop down list in a single cell in Excel
microsoft excelmicrosoft-excel-2010
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
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 > CloseCreate 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 > CloseCreate drop down list > Select Sheet1 > Select cell D2 > Click on Data > Data validation button > Data Validation > Allow: List > Source:
=uniqhall
> OkSecondary 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 > CloseCreate 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 > CloseCreate drop down list > Select Sheet1 > Select cell D5 > Clock on Data > Data validation button > Data validation > Allow: List > Source:
=uniqroom
> OkHope this solves your problem up to some point :)