Am in need of some help and I'm not totally sure where to start in getting this to work. Maybe an XLOOKUP, but I really can't figure out how to lookup one value, and then another, and then produce the result.

I have attached part of the sheet via XL2BB below.

What I need is the table on the right with the data valiadtion date drop down, which is the 14 days of the roster, to populate with the name of the employees rostered to work each shift. The table is currently in the end state, but not sure how to get it there. The cell should first lookup the date in V7, then find that day in the main roster, then look up the shift below, and then finally return the result of the employee in column C.

Hope this makes sense

DIGITAL ROSTER PROJECT 2024 (latest).xlsm | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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 | |||

1 | FORTNIGHT COMMENCING | PUBLISHED | SUNDAY | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | |||||||||||||

2 | 2-Jun | 3-Jun | 4-Jun | 5-Jun | 6-Jun | 7-Jun | 8-Jun | 9-Jun | 10-Jun | 11-Jun | 12-Jun | 13-Jun | 14-Jun | 15-Jun | |||||||||||||||

3 | 02-June-2024 | DAO Name | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | Open Shifts | |||||||||||||

4 | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | |||||||||||||||

5 | |||||||||||||||||||||||||||||

6 | Sunday, 2 June 2024 | ||||||||||||||||||||||||||||

7 | |||||||||||||||||||||||||||||

8 | SHIFT | EMPLOYEE | |||||||||||||||||||||||||||

9 | Line 1 | Rostered DAO | FEEGRADO. R | 9 | Shift | OFF | 1330BC | 1330PB | 1330CFD | 1330PL/S | EDO | OFF | OFF | 1330BC | 1330PB | 1330CFD | 1330PL/S | 1330NRN | OFF | FIXED VALUES! | DYNAMIC VALUES THAT NEED FINDING | ||||||||

10 | Extension Pre | 0530PB | REEVES. S | ||||||||||||||||||||||||||

11 | Covered by DAO | Rostered Times | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 0530PL/S | ENOKA. H | ||||||||||||||||

12 | Remarks | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 0530CFD | N/R | |||||||||||||||||

13 | Leave | Extension Post | 0530NRN | N/R | |||||||||||||||||||||||||

14 | Line 2 | Rostered DAO | RAUF. F | 10 | Shift | 0530NCC | 0530PL/S | OFF | OFF | 0530NRN | 0530BC | 0530PB | 0530NCC | 0530PL/S | OFF | OFF | 0530CFD | 0530PL/S | 0530BC | 0530NCC | RAUF. F | ||||||||

15 | Extension Pre | 0530BC | WILSON. M | ||||||||||||||||||||||||||

16 | Covered by DAO | Rostered Times | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | |||||||||||||||||

17 | Remarks | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 1330PB | BOVELL. S | ||||||||||||||||

18 | Leave | Extension Post | 1330PL/S | THEODOROPOULOS. A | |||||||||||||||||||||||||

19 | Line 3 | Rostered DAO | SALEH. M | 9 | Shift | 2130PL/S | 2130BC | OFF | OFF | OFF | 2130PB | 2130NCC | 2130PL/S | 2130BC | 2130PB | EDO | OFF | 2130PL/S | 2130BC | 1330CFD | N/R | ||||||||

20 | Extension Pre | 1330NRN | N/R | ||||||||||||||||||||||||||

21 | Covered by DAO | Rostered Times | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 1330NCC | MILINOVIC. S | ||||||||||||||||

22 | Remarks | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 1330BC | MUNAGALA. V | |||||||||||||||||

23 | Leave | Extension Post | |||||||||||||||||||||||||||

24 | Line 4 | Rostered DAO | RUBERTO. R | 10 | Shift | OFF | OFF | 1330CFD | 1330PL/S | 1330NRN | 1330BC | 1330PB | 1330NCC | 1330PL/S | OFF | OFF | 1330NRN | 1330BC | 1330PB | 2130PB | SAMI. T | ||||||||

25 | Extension Pre | 2130PL/S | SALEH. M | ||||||||||||||||||||||||||

26 | Covered by DAO | Rostered Times | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 2130NCC | DIBBEN L | |||||||||||||||

27 | Remarks | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 2130BC | SALEH. K | ||||||||||||||||

28 | Leave | Extension Post | |||||||||||||||||||||||||||

29 | Line 5 | Rostered DAO | MEACLEM. H | 9 | Shift | OFF | OFF | 2130PL/S | 2130BC | 2130PB | 2130NCC | 2130PL/S | OFF | 2130PL/S | 2130BC | 2130PB | 2130NCC | EDO | OFF | ||||||||||

30 | Extension Pre | ||||||||||||||||||||||||||||

31 | Covered by DAO | Rostered Times | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | ||||||||||||||||||

32 | Remarks | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | |||||||||||||||||||

33 | Leave | Extension Post | |||||||||||||||||||||||||||

34 | Line 6 | Rostered DAO | ENOKA. H | 10 | Shift | 0530PL/S | 0530NRN | 0530DV | OFF | OFF | 0530NRN | 0530BC | 0530PB | 0530CFD | 0530PL/S | 0530NRN | 0530PB | OFF | OFF | ||||||||||

35 | Extension Pre | ||||||||||||||||||||||||||||

36 | Covered by DAO | Rostered Times | 5:30 | 5:30 | #N/A | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | |||||||||||||||||

37 | Remarks | 13:45 | 13:45 | #N/A | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | ||||||||||||||||||

38 | Leave | Extension Post | |||||||||||||||||||||||||||

39 | Line 7 | Rostered DAO | MILINOVIC. S | 9 | Shift | 1330NCC | 1330PL/S | 1330NRN | 1330BC | EDO | OFF | OFF | OFF | OFF | 1330NRN | 1330BC | 1330PB | 1330CFD | 1330PL/S | ||||||||||

40 | Extension Pre | ||||||||||||||||||||||||||||

41 | Covered by DAO | Rostered Times | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | ||||||||||||||||||

42 | Remarks | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | |||||||||||||||||||

43 | Leave | Extension Post | |||||||||||||||||||||||||||

44 | Line 8 | Rostered DAO | SAMI. T | 10 | Shift | 2130PB | 2130NCC | OFF | OFF | 2130NCC | 2130PL/S | 2130BC | 2130PB | 2130NCC | OFF | OFF | 2130BC | 2130PB | 2130NCC | ||||||||||

45 | Extension Pre | ||||||||||||||||||||||||||||

46 | Covered by DAO | Rostered Times | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | 21:30 | |||||||||||||||||

47 | Remarks | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | 5:45 | ||||||||||||||||||

48 | Leave | Extension Post | |||||||||||||||||||||||||||

49 | Line 9 | Rostered DAO | SVARC. S | 9 | Shift | OFF | EDO | 0530NRN | 0530BC | 0530PB | 0530CFD | OFF | OFF | 0530NRN | 0530BC | 0530PB | OFF | 0530CFD | 0530PL/S | ||||||||||

50 | Extension Pre | ||||||||||||||||||||||||||||

51 | Covered by DAO | Rostered Times | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | 5:30 | ||||||||||||||||||

52 | Remarks | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | 13:45 | |||||||||||||||||||

53 | Leave | Extension Post | |||||||||||||||||||||||||||

54 | Line 10 | Rostered DAO | THEODOROPOLOUS. A | 10 | Shift | 1330PL/S | 1330NRN | 1330BC | OFF | OFF | 1330NRN | 1330BC | 1330PB | 1330CFD | 1330PL/S | 1330NRN | OFF | OFF | 1330NCC | ||||||||||

55 | Extension Pre | ||||||||||||||||||||||||||||

56 | Covered by DAO | Rostered Times | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | |||||||||||||||||

57 | Remarks | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | 21:45 | ||||||||||||||||||

58 | Leave | Extension Post | |||||||||||||||||||||||||||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

G2 | G2 | =IF(A3="","",A3) |

H2:T2 | H2 | =IF(G2="","",G2+1) |

G4,M4:N4,T4 | G4 | =FILTER(DATA!$AF$2:$AF$13,NOT(COUNTIF(G9:G108,DATA!$AF$2:$AF$13))) |

H4:L4,O4:S4 | H4 | =FILTER(DATA!$AG$2:$AG$15,NOT(COUNTIF(H9:H108,DATA!$AG$2:$AG$15))) |

C9,C54,C49,C44,C39,C34,C29,C24,C19,C14 | C9 | =IF($A$3="","",VLOOKUP(CONCATENATE(VALUE($A$3),$A9),DATA!$C$2:$D$1567,2,0)) |

G11:T11,G56:T56,G51:T51,G46:T46,G41:T41,G36:T36,G31:T31,G26:T26,G21:T21,G16:T16 | G11 | =VLOOKUP(G9,DATA!$Z$3:$AB$38,2,FALSE) |

G12:T12,G57:T57,G52:T52,G47:T47,G42:T42,G37:T37,G32:T32,G27:T27,G22:T22,G17:T17 | G12 | =VLOOKUP(G9,DATA!$Z$3:$AB$38,3,FALSE) |

E9,E14,E19,E24,E29,E34,E39,E44,E49,E54 | E9 | =COUNTIF(G9:T9,"*30*") |

Cells with Data Validation | ||
---|---|---|

Cell | Allow | Criteria |

V6:AA7 | List | =$G$2:$T$2 |

C13 | List | =DATA!$X$3:$X$8 |

C58 | List | =DATA!$X$3:$X$8 |

C53 | List | =DATA!$X$3:$X$8 |

C48 | List | =DATA!$X$3:$X$8 |

C43 | List | =DATA!$X$3:$X$8 |

C38 | List | =DATA!$X$3:$X$8 |

C33 | List | =DATA!$X$3:$X$8 |

C28 | List | =DATA!$X$3:$X$8 |

C23 | List | =DATA!$X$3:$X$8 |

C18 | List | =DATA!$X$3:$X$8 |

G19:T19 | List | =DATA!$Z$3:$Z$31 |

G24:T24 | List | =DATA!$Z$3:$Z$31 |

G29:T29 | List | =DATA!$Z$3:$Z$31 |

G34:T34 | List | =DATA!$Z$3:$Z$31 |

G39:T39 | List | =DATA!$Z$3:$Z$31 |

G44:T44 | List | =DATA!$Z$3:$Z$31 |

G49:T49 | List | =DATA!$Z$3:$Z$31 |

G54:T54 | List | =DATA!$Z$3:$Z$31 |

G14:T14 | List | =DATA!$Z$3:$Z$31 |

G9:T9 | List | =DATA!$Z$3:$Z$31 |

C1:E2 | List | =DATA!$AD$3:$AD$4 |

A3:B8 | List | =DATA!$T$3:$T$69 |

C11:D11 | List | =DATA!$V$3:$V$25 |

C16:D16 | List | =DATA!$V$3:$V$25 |

C21:D21 | List | =DATA!$V$3:$V$25 |

C26:D26 | List | =DATA!$V$3:$V$25 |