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 :)
Best Answer
I would make this a comment, but then the information may be lost. You're looking for dependent validation. this page has some good information. And I quote:
Open a new Workbook and on Sheet1 enter these heading into cells A1:D1 Expensive Cars, Cities, Names, Countries
Now place some relevant entries below these headings, down to say row 5. Now select A1:D1, click in the Name Box (left of the formula bar) type the name: List1 and push Enter. Now select A1:D5 (or the last row of the longest list) and go to
Insert>Name>Create. Ensure only "Top Row" is checked and click Ok. If you now go back to the Name Box you will see 5 Named ranges. Note how Excel has used the Underscore for "Expensive_Cars". This is because Named ranges cannot have spaces.Now click onto Sheet2 and select cell A1. Go to Data>Validation, choose the "List" option then type: =List1 in the Source box. Ensure "In-cell dropdown" is checked and click Ok. Now select cell A2 and again go to Data>Validation, choose the "List" option then type:
=INDIRECT(SUBSTITUTE($A$1," ","_"))in the Source box. Ensure "In-cell dropdown" is checked and click Ok.Now select a list from cell A1 and you will get the appropriate list in cell A2.
Notes
INDIRECTFunction return the reference returned by a text string. This means, rather than the Validation List in A2 seeing the content of A1 as a text string, it sees it as a range name .SUBSTITUTEFunction is used to replace any spaces in the text of A1 with a Underscore. This is vital when we have items in List1 like: Expensive Cars.