Forum Discussion
Formula help :)
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.
If you are on Excel with dynamic arrays that could be done with helper ranges which could be created at any place of your workbook (better in separate sheet)
Here
starting from B12 make headers for the time as
=TRANSPOSE($A$2:$A$8)In B13
=SORT(FILTER($A$13:$A$32,COUNTIF(INDEX($B$2:$N$8,MATCH(B$12,$A$2:$A$8,0),0),$A$13:$A$32)=0))(it shows names are not assigned for that time) and drag it to the right.
Select your entire range and add data validation to all cells as
Formula is
=INDEX($B$13:$H$13,0,ROW()-ROW($A$1))#
As for the second question I didn't catch, could you please illustrate in file.
Dear SergeiBaklan ,
Thank you - very helpful!! 🙂
I have tried to illustrate the matter related to my second question - please see attached file.
Best regards,
Maria
Maria,
Second question - I see, thank you. For it you need list of rooms
Formulas like
=FILTER($A$50:$A$62,COUNTIF(INDEX($B$12:$U$18,MATCH(B$49,$A$12:$A$18,0),0),$A$50:$A$62)=0)Data validation
=INDEX($B$50:$H$50,0,ROW()-ROW($A$11))#Hi again SergeiBaklan
I think I didn't succeed in explaining it the right way. 🙂
So what I actually wanted (regarding my second question) is only a change in the way the data will be visualized in the Excel file.
So, actually I only want to be able to pick certain employees and put them into a certain room - not the opposite.
However, in addition to the first scheme, I would also like a scheme showing which rooms each employee should stay in. So it's probably some kind of transpose formula I need here? Unforntunately, I don't know how to do it.
Do you understand what I want? 🙂
Best wishes,
Maria
PS: Hope you will find a solution to my third question aswell - that would be awesome. 🙂
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.
Another questions if okay:
Could you also set up a rule using a formular so that only certain employees (from the list) can be selected for special rooms:
Ex:
ROOM 1: Only AA; AB; AC; AD; AE and AJ can stay hereROOM 2: Only AA, AC, AM, AS and AR can stay here
and so on.... ?
Best regards,
Maria
Selected employees per room - so far have no proper solution, will try to find some later.