Spreadsheet: 55294d1255446255-how-to-populate-cells-with-data-based-on-conditions-from-ranges-example-rota
Sheet: Working Rota

 | Department: |  |  | FRONTOFFICE
 | Shift 1: |  | x
 | Shift 2: |  | x
 | Shift 3: |  | x
 | Shift 4: |  | x
 | Shift 5: |  | x
 | Shift 6: |  | x
 | Day Off |  | o |  | Leave |  | -
 | (Enter "o" (Day Off) or "-" (Leave) in cells under Shift 1 only)
 | WEEK |  | No. | DATES: |  |  |  |  |  |  |  | MONDAY |  |  |  |  |  | TUESDAY |  |  |  |  |  | WEDNESDAY |  |  |  |  |  | THURSDAY |  |  |  |  |  | FRIDAY |  |  |  |  |  | SATURDAY |  |  |  |  |  | SUNDAY |  |  |  |  |  |  |  | WEEK | DEPARTMENT | ADMINISTRATION | FRONTOFFICE | GUESTSERVICES | MAINKITCHEN | STAFFKITCHEN | STEWARDING | FOODBEVERAGE | HOUSEKEEPING | ENGINEERING | MAINTENANCE | HUMANRESOURCES | ACCOUNTING
 |  |  |  |  |  |  |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  |  |  | No. | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>> | <<SELECT>>
 | Name |  |  |  |  |  | Position |  |  |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 |  |  | 1 | ADMINISTRATION | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD | HOLD
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | FRONTOFFICE | General Manager | Manager | Fitness Trainer 1 | Exec. Chef | Supervisor 1 | Chief [NAME] | F&B Manager | HK Manager | Chief Engineer | Pool 1 | HR Manager | Chief Accountant
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3 | GUESTSERVICES | Personal Assistant | Supervisor 1 | Fitness [NAME] | Sous Chef | Supervisor 2 | Steward 1 M | Supervisor 1 | Supervisor 1 | Supervisor 1 | Pool 2 | Secretary | Purchasing
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | MAINKITCHEN | Senior Secretary | Supervisor 2 | Guest Activity 1 | Pastry Chef | Cook 1 | [NAME] | Supervisor 2 | Supervisor 2 | Supervisor 2 | Pool 3 | [NAME] | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 5 | STAFFKITCHEN | Secretary | Supervisor 3 | Guest Activity 2 | Cook 1 | [NAME] | Steward 3 M | Supervisor 3 | Supervisor 3 | Supervisor 3 | Pool 4 | [NAME] | Accountant 1
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 6 | STEWARDING |  | PABX 1 | Guest Activity 3 | [NAME] | Cook 3 | [NAME] | Waiter 1 C | Room Attendant 1 | Engineer 1 | Pool 5 | [NAME] | Accountant 2
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 7 | FOODBEVERAGE |  | PABX 2 | Guest Activity 4 | Cook 3 | Cook 4 | Steward 5 M | Waiter 2 C | Room Attendant 2 | Engineer 2 | Pool 6 | [NAME] | Accountant 3
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8 | HOUSEKEEPING |  | PABX 3 | Guest Activity 5 | Cook 4 |  | [NAME] | Waiter 3 C | Room Attendant 3 | Engineer 3 | Pool 7 | Clerk 5 | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 9 | ENGINEERING |  | Reception 1 | [NAME] | Cook 5 |  | [NAME] | [NAME] C | [NAME] | Engineer 4 | Pool 8 |  | Accountant 5
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 | MAINTENANCE |  | Reception 2 | [NAME] | Bakery 1 |  | [NAME] | Waiter 5 C | Room Attendant 5 | Engineer 5 | Pool 9
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 11 | HUMANRESOURCES |  | Reception 3 | [NAME] | Bakery 2 |  |  | Waiter 6 C | [NAME] | Engineer 6 | Garden/Grounds 1
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 12 | ACCOUNTING |  | Reception 4 | Driver 1 G | Bakery 3 |  |  | Waiter 7 C | Room Attendant 7 | Engineer 7 | Garden/Grounds 2
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 13 |  |  | Reception 5 | Driver 2 G | Pastry 1 |  |  | Waiter 8 C | Room Attendant 8 | Engineer 8 | Garden/Grounds 3
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 14 |  |  | Guest Activities 1 | Driver 3 G | Pastry 2 |  |  | Waiter 9 C | [NAME] 9 | Engineer 9 | Garden/Grounds 4
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 15 |  |  | Guest Activities 2 | Driver 4 G | Pastry 3 |  |  | [NAME] | Public/Offices 1 | Painter 1 | Garden/Grounds 5
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 16 |  |  | Guest Activities 3 | Driver 5 G | Cold 1 |  |  | [NAME] | Public/Offices 2 | Painter 2 | Garden/Grounds 6
 | Total Staff On-Duty |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17 |  |  | [NAME] | Driver 1 S | Cold 2 |  |  | Waiter 3 G | Public/Offices 3 | Carpenter 1 | Garden/Grounds 7
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 18 |  |  | [NAME] | Driver 2 S | Cold 3 |  |  | [NAME] | Public/Offices 4 | [NAME] | Garden/Grounds 8
 | WEEK |  | No. | DATES: |  |  |  |  |  |  |  | MONDAY |  |  |  |  |  | TUESDAY |  |  |  |  |  | WEDNESDAY |  |  |  |  |  | THURSDAY |  |  |  |  |  | FRIDAY |  |  |  |  |  | SATURDAY |  |  |  |  |  | SUNDAY |  |  |  |  |  |  |  | 19 |  |  | [NAME] | Driver 3 S | Butcher |  |  | Waiter 5 G | Public/Offices 5 |  | Garden/Grounds 9
 |  |  |  |  |  |  |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  |  |  | 20 |  |  | Bell Boy 4 |  | [NAME] |  |  | Bartender 1
 | Name |  |  |  |  |  | Position |  |  |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 |  |  | 21 |  |  | Bell Boy 5 |  |  |  |  | Bartender 2
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 22 |  |  | Night Manager |  |  |  |  | Bartender 3
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 23 |  |  | Night Duty 1 |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 24 |  |  | Night Duty 2 |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 25 |  |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 26 |  |  |  |  |  |  |  | Beach 1
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 27 |  |  |  |  |  |  |  | Beach 2
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 28 |  |  |  |  |  |  |  | Beach 3
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 29 |  |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 30 |  |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 31 |  |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 32 |  |  |  |  |  |  |  | Room Service 1
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 33 |  |  |  |  |  |  |  | Room Service 2
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 34 |  |  |  |  |  |  |  | Room Service 3
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 35 |  |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 36
 | Total Staff On-Duty |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 37
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 38
 | WEEK |  | No. | DATES: |  |  |  |  |  |  |  | MONDAY |  |  |  |  |  | TUESDAY |  |  |  |  |  | WEDNESDAY |  |  |  |  |  | THURSDAY |  |  |  |  |  | FRIDAY |  |  |  |  |  | SATURDAY |  |  |  |  |  | SUNDAY |  |  |  |  |  |  |  | 39
 |  |  |  |  |  |  |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  |  |  | 40
 | Name |  |  |  |  |  | Position |  |  |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 |  |  | 41
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 42
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 43
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 44
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 45
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 46
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 47
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 48
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 49
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 50
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 51
 |  |  |  |  |  |  | <<SELECT>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 52
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 | Total Staff On-Duty
 | WEEK |  | No. | DATES: |  |  |  |  |  |  |  | MONDAY |  |  |  |  |  | TUESDAY |  |  |  |  |  | WEDNESDAY |  |  |  |  |  | THURSDAY |  |  |  |  |  | FRIDAY |  |  |  |  |  | SATURDAY |  |  |  |  |  | SUNDAY
 |  |  |  |  |  |  |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift |  |  |  |  |  | Shift
 | Name |  |  |  |  |  | Position |  |  |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 |  |  |  |  |  |  | <<SELECT>>
 | Total Staff On-Duty