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

Question

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

Answer 1

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 :)

Answer 2

You really can't use the same cell for that.

What you can do is use the cell next to it. While It looks like Robert Schmidts answer will work, an alternative (and to my mind simpler) method for the same feature would be listed here.

It uses the same kind of idea, but only requires a single, simple formula. From the description of your problem you don't actually need Dynamic Ranges (cool as they are).

The gist of the method is to arrange your user selections in a single table with the first selection on the left, then each of the subsequent selections is listed in a different column to the right.

e.g. Hallway #34 | Room 1 | Room 2 | Room 3.....

Then using the Name Manger and the create names from selection tool, you automatically assign each Hallway in the first column as a 'Name' to the rooms in the same row.

Finally you use the Data Validation by list feature to create your drop down lists, on for the Hallway selection, and a second one for the room selection. You make the second one dependant on the first by using a simple =INDIRECT formula in the data validation of the second column.

I'd suggest popping down to the comments section to, as a user there has pointed out that you can use the =SUBSTITUTE function to deal with the spaces in your Hallway and Room values.