Hello there excel experts !
This project is about a school that has 4 slots for supervising kids....2 slots at the yard, one slot at the ground floor and one at the1st floor.
There are two time shifts every day. Some of the professors can supervise at the 1st shift and some at the 2nd shift, for each weekday Monday to Friday.
These two shifts are being defined by the times that each professor has a class. Shift A is 1st to 4th hour and Shift B is 4th to 7th hour.
Some professors can fill the slot in both shifts. Also one prerequisite is that they should not supervise more that 3 times each....
Now my request for you guys:
The code should be able to find which professors have lessons (cells B3:AJ20) between 1st and 4th hour, which ones have lessons between 4th and 7 th hour.
And then I need a formula or code to randomly populate 40 cells (B22:AH25), from a list of values (text names of the professors) that are located at at cells A3:A20
Professors that have lessons at the first supervizing shift should populate Shift A first and professor that have lessons at the second shift should Populate Shift B.
Some professors as I already mentioned could have lessons at the whole span of hours 1st to 7th so they could populate both shifts.
These names cannot be chosen more that 3 times and they all have to be chosen of course,, so all shifts and supervizing slots can be populated !
I hope I was clear and if you have any questions please fell free to ask !
Thanks in advance,
John
This project is about a school that has 4 slots for supervising kids....2 slots at the yard, one slot at the ground floor and one at the1st floor.
There are two time shifts every day. Some of the professors can supervise at the 1st shift and some at the 2nd shift, for each weekday Monday to Friday.
These two shifts are being defined by the times that each professor has a class. Shift A is 1st to 4th hour and Shift B is 4th to 7th hour.
Some professors can fill the slot in both shifts. Also one prerequisite is that they should not supervise more that 3 times each....
Now my request for you guys:
The code should be able to find which professors have lessons (cells B3:AJ20) between 1st and 4th hour, which ones have lessons between 4th and 7 th hour.
And then I need a formula or code to randomly populate 40 cells (B22:AH25), from a list of values (text names of the professors) that are located at at cells A3:A20
Professors that have lessons at the first supervizing shift should populate Shift A first and professor that have lessons at the second shift should Populate Shift B.
Some professors as I already mentioned could have lessons at the whole span of hours 1st to 7th so they could populate both shifts.
These names cannot be chosen more that 3 times and they all have to be chosen of course,, so all shifts and supervizing slots can be populated !
I hope I was clear and if you have any questions please fell free to ask !
Thanks in advance,
John
Week Shift Shedule.xlsx | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | |||
1 | PROGRAM | Monday | Tuesday | Wednesday | Thursday | Friday | ||||||||||||||||||||||||||||||||
2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |||
3 | ΒΑΣΙΛΟΠΟΥΛΟΥ ΕΛΕΥΘΕΡΙΑ | ΑΛΓ Α3 | ΑΛΓ Β2 | ΑΛΓ Α2 | ΜΑΘ Γ1 | ΜΑΘ Β2/Β3 | ΑΛΓ Α3 | ΜΑΘ Γ1 | ΑΛΓ Α3 | ΑΛΓ Α2 | ΓΕΩΜ Α3 | ΓΕΩΜ Α3 | ΑΛΓ Β2 | ΜΑΘ Β2/Β3 | ΑΛΓ Α2 | ΜΑΘ Β2/Β3 | ΑΛΓ Β2 | |||||||||||||||||||||
4 | ΓΚΕΤΣΟΥ ΕΛΕΝΗ | ΓΥΜ Α3 | ΓΥΜ Β3 | |||||||||||||||||||||||||||||||||||
5 | ΔΑΝΔΟΥΛΑΚΗΣ ΓΙΩΡΓΟΣ | ΠΛΗΡ Γ3 | ΠΛΗΡ Γ4 | ΠΛΗΡ Β1 | ΠΛΗΡ Γ3 | ΠΛΗΡ Γ4 | ΠΛΗΡ Γ3 | ΠΛΗΡ Β2 | ΠΛΗΡ Γ3 | ΠΛΗΡ Β2 | ΠΛΗΡ Α1 | ΠΛΗΡ Β3 | ΠΛΗΡ Γ4 | ΠΛΗΡ Γ4 | ΠΛΗΡ Α1 | ΠΛΗΡ Γ3 | ΠΛΗΡ Γ4 | ΠΛΗΡ Β3 | ΠΛΗΡ Γ4 | ΠΛΗΡ Γ3 | ΠΛΗΡ Β1 | |||||||||||||||||
6 | ΔΗΜΗΤΡΟΠΟΥΛΟΥ ΝΕΚΤΑΡΙΑ | ΛΟΓ Α4 | ΛΑΤ Β1/Β2/Β3 | ΛΑΤ Γ1 | ΛΑΤ Γ1 | ΓΛΣ Α4 | ΦΛΣ Β1 | ΛΑΤ Γ1 | ΛΑΤ Β1/Β2/Β3 | ΛΑΤ Γ1 | ΛΑΤ Γ1 | ΦΛΣ Β3 | ΦΛΣ Β1 | ΛΑΤ Γ1 | ΛΟΓ Α4 | ΦΛΣ Β3 | ||||||||||||||||||||||
7 | ΚΩΝΣΤΑΝΤΕΛΛΟΥ ΑΘΗΝΑ | ΓΛΣ Β3 | ΙΣΤ Γ1 | ΛΟΓ Γ4 | ΙΣΤ Γ1 | ΙΣΤ Α2 | ΛΟΓ Β3 | ΓΛΣ Γ4 | ΙΣΤ Α1 | ΛΟΓ Γ4 | ΙΣΤ Γ1 | ΙΣΤ Α2 | ΛΟΓ Β3 | ΛΟΓ Γ4 | ΙΣΤ Γ1 | ΙΣΤ Γ1 | ΛΟΓ Γ4 | ΙΣΤ Γ1 | ΙΣΤ Α1 | |||||||||||||||||||
8 | ΛΕΝΤΖΙΟΥ ΣΤΕΛΛΑ | ΑΡΧ Α2 | ΛΟΓ Γ1 | ΓΛΣ Α3 | ΙΣΤ Α4 | ΙΣΤ Β3 | ΑΡΧ Α2 | ΛΟΓ Γ1 | ΙΣΤ Β1 | ΙΣΤ Β2 | ΛΟΓ Α3 | ΑΡΧ Α2 | ΛΟΓ Γ1 | ΙΣΤ Β3 | ΙΣΤ Β1 | ΙΣΤ Β2 | ΙΣΤ Α4 | ΑΡΧ Α2 | ΛΟΓ Α3 | ΑΡΧ Α2 | ΛΟΓ Γ1 | ΓΛΣ Γ1 | ||||||||||||||||
9 | ΜΑΡΕΛΗ ΕΛΕΥΘΕΡΙΑ | ΑΡΧ Γ1 | ΛΟΓ Γ2 | ΑΡΧ Γ1 | ΑΡΧ Α1 | ΛΟΓ Α2 | ΛΟΓ Γ2 | ΑΡΧ Γ1 | ΛΟΓ Α2 | ΑΡΧ Α1 | ΑΡΧ Γ1 | ΛΟΓ Γ2 | ΓΛΣ Γ2 | ΑΡΧ Α1 | ΓΛΣ Α2 | ΑΡΧ Α1 | ΑΡΧ Γ1 | ΑΡΧ Γ1 | ΛΟΓ Γ2 | ΑΡΧ Α1 | ||||||||||||||||||
10 | ΜΥΡΟΓΙΑΝΝΗ ΑΝΔΡΟΝΙΚΗ | ΦΥΣ Β3 | ΦΥΣ Β2 | ΦΥΣ Γ2 | ΦΥΣ_ΠΡΟΣ Β1/Β2 | ΦΥΣ Α2 | ΦΥΣ Α1 | ΦΥΣ Γ2 | ΦΥΣ Α2 | ΦΥΣ Β3 | ΦΥΣ_ΠΡΟΣ Β1/Β2 | ΦΥΣ Β1 | ΦΥΣ Γ2 | ΦΥΣ Γ2 | ΦΥΣ Β2 | ΦΥΣ Α1 | ΦΥΣ Γ2 | ΦΥΣ Β1 | ΦΥΣ Γ2 | |||||||||||||||||||
11 | ΠΑΝΑΡΙΤΗΣ ΑΝΑΣΤΑΣΙΟΣ | ΓΕΩΜ Β1 | ΓΕΩΜ Α1 | ΜΑΘ Γ2/Γ3 | ΜΑΘ Β1/Β2 | ΜΑΘ Γ2/Γ3 | ΑΛΓ Β1 | ΑΛΓ Β1 | ΜΑΘ Γ2/Γ3 | ΓΕΩΜ Β2 | ΓΕΩΜ Β2 | ΜΑΘ Γ2/Γ3 | ΜΑΘ Β1/Β2 | ΓΕΩΜ Β1 | ΜΑΘ Γ2/Γ3 | ΓΕΩΜ Α1 | ΑΛΓ Β1 | ΜΑΘ Γ2/Γ3 | ΜΑΘ Β1/Β2 | |||||||||||||||||||
12 | ΠΑΠΑΔΗΜΗΤΡΙΟΥ ΜΑΡΙΑ | ΙΣΤ Γ4 | ΑΡΧ Α3 | ΛΟΓ Β1 | ΑΡΧ Β2 | ΛΟΓ Β2 | ΙΣΤ Γ4 | ΓΛΣ Β1 | ΙΣΤ Γ2 | ΑΡΧ Α3 | ΑΡΧ Β3 | ΑΡΧ Α3 | ΑΡΧ Β2 | ΙΣΤ Γ3 | ΛΟΓ Β1 | ΙΣΤ Γ3 | ΑΡΧ Β3 | ΙΣΤ Γ2 | ΑΡΧ Α3 | ΛΟΓ Β2 | ΓΛΣ Β2 | ΑΡΧ Α3 | ||||||||||||||||
13 | ΠΕΡΔΙΚΑΡΗ ΒΑΡΒΑΡΑ | ΑΓΓΛ Α2 | ΑΓΓΛ Α1 | ΑΓΓΛ Γ2 | ΑΓΓΛ Β3 | ΑΓΓΛ Γ3 | ΑΓΓΛ Α2 | ΑΓΓΛ A4 | ΑΓΓΛ Α3 | ΑΓΓΛ Α1 | ΑΓΓΛ Γ1 | ΑΓΓΛ Γ4 | ΑΓΓΛ Α4 | ΑΓΓΛ Β2 | ΑΓΓΛ Β1 | ΑΓΓΛ Γ1 | ΑΓΓΛ Α3 | ΑΓΓΛ Β1 | ΑΓΓΛ Γ2 | ΑΓΓΛ Β2 | ΑΓΓΛ Γ3 | ΑΓΓΛ Β3 | ΑΓΓΛ Α1 | ΑΓΓΛ Γ4 | ||||||||||||||
14 | ΠΗΛΙΔΗ ΑΛΕΞΑΝΔΡΑ | ΧΗΜ Γ2 | ΧΗΜ Α2 | ΧΗΜ Α4 | ΦΥΣ_ΠΡΟΣ Β2/Β3 | ΧΗΜ Γ2 | ΧΗΜ Α3 | ΧΗΜ Α1 | ΧΗΜ Γ2 | ΧΗΜ Β3 | ΦΥΣ Α3 | ΧΗΜ Γ2 | ΦΥΣ_ΠΡΟΣ Β2/Β3 | ΦΥΣ Α4 | ΧΗΜ Α3 | ΧΗΜ Γ2 | ΦΥΣ Α4 | ΧΗΜ Α2 | ΧΗΜ Α1 | ΧΗΜ Α4 | ΦΥΣ Α3 | ΧΗΜ Γ2 | ||||||||||||||||
15 | ΠΛΑΤΣΗ ΜΑΡΙΑΝΘΗ | ΓΥΜ Β2 | ΓΥΜ Β1 | ΓΥΜ Α4 | ΓΥΜ Α1 | ΓΥΜ Α2 | ||||||||||||||||||||||||||||||||
16 | ΡΑΦΤΟΓΙΑΝΝΗ ΘΑΛΕΙΑ | ΑΛΓ Α1 | ΜΑΘ Γ2/Γ4 | ΑΛΓ Β3 | ΑΛΓ Α4 | ΜΑΘ Γ2/Γ | ΓΕΩΜ Β3 | ΑΛΓ Α1 | ΓΕΩΜ Α4 | ΜΑΘ Γ2/Γ4 | ΓΕΩΜ Α2 | ΑΛΓ Β3 | ΑΛΓ Α1 | ΜΑΘ Γ2/Γ4 | ΓΕΩΜ Α4 | ΑΛΓ Α4 | ΜΑΘ Γ2/Γ4 | ΑΛΓ Α4 | ΑΛΓ Β3 | ΓΕΩΜ Β3 | ΜΑΘ Γ2/Γ4 | ΓΕΩΜ Α2 | ||||||||||||||||
17 | ΡΕΝΤΕΣΗ ΓΕΩΡΓΙΑ | ΒΙΟΛ Α4 | ΒΙΟΛ Β1 | ΒΙΟΛ Γ2 | ΒΙΟΛ Α2 | ΧΗΜ Β1 | ΒΙΟΛ Α1 | ΒΙΟΛ Γ2 | ΒΙΟΛ Β2 | ΧΗΜ Β2 | ΒΙΟΛ Β3 | ΒΙΟΛ Α3 | ΒΙΟΛ Α1 | ΒΙΟΛ Γ2 | ΧΗΜ Β1 | ΒΙΟΛ Β1 | ΒΙΟΛ Γ2 | ΒΙΟΛ Β3 | ΒΙΟΛ Α2 | ΒΙΟΛ Γ2 | ΒΙΟΛ Β2 | ΒΙΟΛ Α3 | ΒΙΟΛ Α4 | ΒΙΟΛ Γ2 | ||||||||||||||
18 | ΤΕΧΛΕΜΕΤΖΗ ΑΛΕΞΑΝΔΡΑ | ΟΙΚ Γ3 | ΠΟΛ.ΠΔ Α3 | ΟΙΚ Γ4 | ΠΟΛ.ΠΔ A1 | ΟΙΚ Γ3 | ΟΙΚ Γ4 | ΟΙΚ Γ3 | ΟΙΚ Γ4 | ΠΟΛ.ΠΔ Α2 | ΟΙΚ Γ3 | ΠΟΛ.Π Α3 | ΟΙΚ Γ4 | ΟΙΚ Γ3 | ΟΙΚ Γ3 | ΠΟΛ.ΠΔ Α2 | ΟΙΚ Γ4 | ΟΙΚ Γ4 | ΠΟΛ.ΠΔ Α1 | |||||||||||||||||||
19 | ΤΣΙΑΜΑΛΟΥ ΣΤΑΥΡΟΥΛΑ | ΑΡΧ Α4 | ΛΟΓ Α1 | ΛΟΓ Γ3 | ΓΛΣ Γ3 | ΑΡΧ_ΠΡΟΣ Β1/Β2/Β3 | ΦΛΣ Β2 | ΙΣΤ Α3 | ΑΡΧ Α4 | ΛΟΓ Γ3 | ΛΟΓ Γ3 | ΑΡΧ Α4 | ΓΛΣ Α1 | ΑΡΧ Α4 | ΛΟΓ Α1 | ΛΟΓ Γ3 | ΑΡΧ_ΠΡΟΣ Β1/Β2/Β3 | ΑΡΧ Β1 | ΙΣΤ Α3 | ΑΡΧ Α4 | ΑΡΧ_ΠΡΟΣ Β1/Β2/Β3 | ΑΡΧ Β1 | ||||||||||||||||
20 | ΤΣΙΡΩΝΗΣ ΧΡΗΣΤΟΣ | ΠΟΛ.Π Α4 | ΠΟΛ.ΠΔ Α4 | |||||||||||||||||||||||||||||||||||
21 | SHIFTS | SHIFT A' | SHIFT B' | SHIFT A' | SHIFT B' | SHIFT A' | SHIFT B' | SHIFT A' | SHIFT B' | SHIFT A' | SHIFT B' | |||||||||||||||||||||||||||
22 | YARD | ΛΕΝΤΖΙΟΥ ΣΤΕΛΛΑ | ΠΕΡΔΙΚΑΡΗ ΒΑΡΒΑΡΑ | ΔΑΝΔΟΥΛΑΚΗΣ ΓΙΩΡΓΟΣ | ΚΩΝΣΤΑΝΤΕΛΛΟΥ ΑΘΗΝΑ | ΡΑΦΤΟΓΙΑΝΝΗ ΘΑΛΕΙΑ | ΠΑΝΑΡΙΤΗΣ ΑΝΑΣΤΑΣΙΟΣ | ΒΑΣΙΛΟΠΟΥΛΟΥ ΕΛΕΥΘΕΡΙΑ | ΡΑΦΤΟΓΙΑΝΝΗ ΘΑΛΕΙΑ | ΜΥΡΟΓΙΑΝΝΗ ΑΝΔΡΟΝΙΚΗ | ΛΕΝΤΖΙΟΥ ΣΤΕΛΛΑ | |||||||||||||||||||||||||||
23 | ΜΑΡΕΛΗ ΕΛΕΥΘΕΡΙΑ | ΠΗΛΙΔΗ ΑΛΕΞΑΝΔΡΑ | ΠΑΝΑΡΙΤΗΣ ΑΝΑΣΤΑΣΙΟΣ | ΠΕΡΔΙΚΑΡΗ ΒΑΡΒΑΡΑ | ΤΕΧΛΕΜΕΤΖΗ ΑΛΕΞΑΝΔΡΑ | ΜΥΡΟΓΙΑΝΝΗ ΑΝΔΡΟΝΙΚΗ | ΚΩΝΣΤΑΝΤΕΛΛΟΥ ΑΘΗΝΑ | ΠΑΝΑΡΙΤΗΣ ΑΝΑΣΤΑΣΙΟΣ | ΜΑΡΕΛΗ ΕΛΕΥΘΕΡΙΑ | ΤΣΙΑΜΑΛΟΥ ΣΤΑΥΡΟΥΛΑ | ||||||||||||||||||||||||||||
24 | GROUN FLOOR | ΜΥΡΟΓΙΑΝΝΗ ΑΝΔΡΟΝΙΚΗ | ΠΑΠΑΔΗΜΗΤΡΙΟΥ ΜΑΡΙΑ | ΠΑΠΑΔΗΜΗΤΡΙΟΥ ΜΑΡΙΑ | ΜΑΡΕΛΗ ΕΛΕΥΘΕΡΙΑ | ΠΑΠΑΔΗΜΗΤΡΙΟΥ ΜΑΡΙΑ | ΚΩΝΣΤΑΝΤΕΛΛΟΥ ΑΘΗΝΑ | ΠΗΛΙΔΗ ΑΛΕΞΑΝΔΡΑ | ΡΕΝΤΕΣΗ ΓΕΩΡΓΙΑ | ΔΑΝΔΟΥΛΑΚΗΣ ΓΙΩΡΓΟΣ | ΤΕΧΛΕΜΕΤΖΗ ΑΛΕΞΑΝΔΡΑ | |||||||||||||||||||||||||||
25 | 1ST FLOOR | ΡΕΝΤΕΣΗ ΓΕΩΡΓΙΑ | ΤΣΙΑΜΑΛΟΥ ΣΤΑΥΡΟΥΛΑ | ΛΕΝΤΖΙΟΥ ΣΤΕΛΛΑ | ΤΕΧΛΕΜΕΤΖΗ ΑΛΕΞΑΝΔΡΑ | ΡΕΝΤΕΣΗ ΓΕΩΡΓΙΑ | ΒΑΣΙΛΟΠΟΥΛΟΥ ΕΛΕΥΘΕΡΙΑ | ΔΑΝΔΟΥΛΑΚΗΣ ΓΙΩΡΓΟΣ | ΤΣΙΑΜΑΛΟΥ ΣΤΑΥΡΟΥΛΑ | ΠΗΛΙΔΗ ΑΛΕΞΑΝΔΡΑ | ΠΕΡΔΙΚΑΡΗ ΒΑΡΒΑΡΑ | |||||||||||||||||||||||||||
PROGRAM |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B22:E25 | List | =$A$3:$A$20 |
F22:H25 | List | =$A$3:$A$20 |
I22:L25 | List | =$A$3:$A$20 |
M22:O25 | List | =$A$3:$A$20 |
P22:S25 | List | =$A$3:$A$20 |
T22:V25 | List | =$A$3:$A$20 |
W22:Z25 | List | =$A$3:$A$20 |
AA22:AC25 | List | =$A$3:$A$20 |
AD22:AG25 | List | =$A$3:$A$20 |
AH22:AJ25 | List | =$A$3:$A$20 |