Formula help :)

mariasolhoej 

Third question. Solution is bit straightforward, but perhaps works correctly.

So, in Room Allowance sheet we keep matrix like this

That's the only source for employee and room names, in all other places we reference on it.

Next is Helper sheet (could be hided) where we keep combinations of all available periods and rooms and build for each such combination list of employees allowed to book the room.

 In C4

=INDEX(Booking!$C$3:$O$3,1,INT((COLUMN()-COLUMN($B$5)-1)/COUNTA(Booking!$B$4:$B$10))+1)

drag it to the right till error appears.

In C5

=INDEX(Booking!$B$4:$B$10,MOD((COLUMN()-COLUMN($B$5)-1),COUNTA(Booking!$B$4:$B$10))+1,1)

and in C6

=SORT(FILTER(
              'Rooms allowance'!$B$3:$B$22,
              (COUNTIF(INDEX(Booking!$C$4:$O$10,MATCH(C$5,Booking!$B$4:$B$10,0),0),'Rooms allowance'!$B$3:$B$22)=0)*
              (INDEX('Rooms allowance'!$C$3:$O$22,0,XMATCH(C$4,'Rooms allowance'!$C$2:$O$2))="x")
))

Drag them also to the right (or select the range and CTRL+R).

Data validation for entire booking range

is with formula

=INDEX(Helper!$C$6:$CO$6,1,XMATCH(C$3&$B4,Helper!$C$4:$CO$4&Helper!$C$5:$CO$5))#

Solution is very depends on number of employees, I assumed relatively small one.

Another variant which I tried, without helper, doesn't work since data validation has 256 characters limit on formula length and now I don't see the way how to fit it.