or with some helper cells/formulas.

[K1] ="Vacant" => as hard coded variable

[L1] = COUNTIF(F:F,K1) => the number of item that should show up in the list

[J2] = IF(ROW(E1)<=$L$1,INDEX($E$3:$E$12,AGGREGATE(15,6,ROW(OFFSET($E$1,,,COUNTA($E:$E)))/(COUNTIF($K$1,$F$3:$F$12)),ROW(E1))),"")

- the IF ( ) checks if the actual position in the list exceeds the number of items. If so, the cell remains "empty".
- INDEX($E$3:$E$12 is the range where the list needs to be extracted from (could be made dynamic or set to a large enough range)
- ROW(OFFSET($E$1,,,COUNTA($E:$E))) constructs a dynamic range based on the number of rooms in the original list and contains a list of positions or "rows"
- (COUNTIF($K$1,$F$3:$F$12)) notice the inverse of arguments: it gives the positions of "vacant" in the list
- using a division between both gives {1,#DIV/0!,3,#DIV/0!,5,6,7,#DIV/0!,#DIV/0!,#DIV/0!}
- AGGREGATE(15,6 will select the smallest value excluding the errors.
- the exact position is returned via ROW(E1). Dragging down the formula and since the row reference is relative the position returned is always +1.

Finally in the name manager RoomDropDown

=Sheet1!$J$2:INDEX(Sheet1!$J:$J,Sheet1!$L$1+1) is a dynamic range starting from J2 down to the number counted. Because there is a title in the way I add one to the counter.

Use this name in the drop down definition.