VLOOKUP?

M

MeaclH

New Member
Joined
Apr 2, 2014
Messages
47
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey all,
Never really been one to understand VLOOKUP in this much detail, but need some help figuring out how to make this work. I have attached a screen shot with what I am kind of hoping to achieve. (also figured out XL2BB so there is a minisheet attached.
I am working on a digital roster, and this requires a daily sheet to be generated with who is working what shift.

In cell V7, is a data validation table with the 14 days of the roster from range G2:T2. When each date is selected I would like the shift names in column V, 0530PB,0530PL/S etc etc to have the name of whoever is rostered that shift to be entered next to the shift in column Y.

This is further complicated by the fact that there may, be a staff member in C9,C14,C19 etc etc who is being covered. Therefore if there is data in the cell below, would prefer if it if the name from the cell below is entered instead.

Hopefully this makes sense, what I am trying to achieve. Let me know if you have any questions.

Thanks for your help!

Hayden

DIGITAL ROSTER PROJECT 2024 (latest).xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1FORTNIGHT COMMENCINGPUBLISHEDSUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
22-Jun3-Jun4-Jun5-Jun6-Jun7-Jun8-Jun9-Jun10-Jun11-Jun12-Jun13-Jun14-Jun15-Jun
302-June-2024DAO NameOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen Shifts
4#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#VALUE!#NAME?#NAME?#VALUE!#VALUE!#VALUE!#VALUE!
5
6
7Sunday, 2 June 2024
8
9Line 1Rostered DAOFEEGRADO. R9ShiftOFF1330BC1330PB1330CFD1330PL/SEDOOFFOFF1330BC1330PB1330CFD1330PL/S1330NRNOFF
10Extension Pre0530PB
11Covered by DAORostered Times 13:3013:3013:3013:30 13:3013:3013:3013:3013:30 0530PL/S
12Remarks 21:4521:4521:4521:45 21:4521:4521:4521:4521:45 0530CFD
13LeaveExtension Post0530NRN
14Line 2Rostered DAORAUF. F10Shift0530NCC0530PL/SOFFOFF0530NRN0530BC0530PB0530NCC0530PL/SOFFOFF0530CFD0530PL/S0530BC0530NCC
15Extension Pre0530BC
16Covered by DAORostered Times5:305:30 5:305:305:305:305:30 5:305:305:30
17Remarks13:4513:45 13:4513:4513:4513:4513:45 13:4513:4513:451330PB
18LeaveExtension Post1330PL/S
19Line 3Rostered DAOSALEH. M9Shift2130PL/S2130BCOFFOFFOFF2130PB2130NCC2130PL/S2130BC2130PBEDOOFF2130PL/S2130BC1330CFD
20Extension Pre1330NRN
21Covered by DAORostered Times21:3021:30 21:3021:3021:3021:3021:30 21:3021:301330NCC
22Remarks5:455:45 5:455:455:455:455:45 5:455:451330BC
23LeaveExtension Post
24Line 4Rostered DAORUBERTO. R10ShiftOFFOFF1330CFD1330PL/S1330NRN1330BC1330PB1330NCC1330PL/SOFFOFF1330NRN1330BC1330PB2130PB
25Extension Pre2130PL/S
26Covered by DAORostered Times 13:3013:3013:3013:3013:3013:3013:30 13:3013:3013:302130NCC
27Remarks 21:4521:4521:4521:4521:4521:4521:45 21:4521:4521:452130BC
28LeaveExtension Post
29Line 5Rostered DAOMEACLEM. H9ShiftOFFOFF2130PL/S2130BC2130PB2130NCC2130PL/SOFF2130PL/S2130BC2130PB2130NCCEDOOFF
30Extension Pre
31Covered by DAORostered Times 21:3021:3021:3021:3021:30 21:3021:3021:3021:30
32Remarks 5:455:455:455:455:45 5:455:455:455:45
33LeaveExtension Post
Sheet1
Cell Formulas
RangeFormula
G2G2=IF(A3="","",A3)
H2:T2H2=IF(G2="","",G2+1)
G4,T4,M4:N4G4=_xlfn._xlws.FILTER(DATA!$AF$2:$AF$13,NOT(COUNTIF(G9:G108,DATA!$AF$2:$AF$13)))
O4:S4,H4:L4H4=_xlfn._xlws.FILTER(DATA!$AG$2:$AG$15,NOT(COUNTIF(H9:H108,DATA!$AG$2:$AG$15)))
C9,C29,C24,C19,C14C9=IF($A$3="","",VLOOKUP(CONCATENATE(VALUE($A$3),$A9),DATA!$C$2:$D$1567,2,0))
G31:T31,G26:T26,G21:T21,G16:T16,G11:T11G11=VLOOKUP(G9,DATA!$Z$3:$AB$38,2,FALSE)
G32:T32,G27:T27,G22:T22,G17:T17,G12:T12G12=VLOOKUP(G9,DATA!$Z$3:$AB$38,3,FALSE)
E9,E29,E24,E19,E14E9=COUNTIF(G9:T9,"*30*")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N9:O9Cell ValueduplicatestextNO
G10:T10,G13:T13,G15:T15,G18:T18,G20:T20,G23:T23,G25:T25,G28:T28,G30:T30,G33:T33,G35:T35,G38:T38,G40:T40,G43:T43,G45:T45,G48:T48,G50:T50,G53:T53,G55:T55,G58:T58,G60:T60,G63:T63,G65:T65,G68:T68,G70:T70,G73:T73,G75:T75,G78:T78,G80:T80,G83:T83,G85:T85,G88:T88Cell Valueending with "?"textNO
G30:T33Expression=OR(G$29="PHC",G$29="LSL",G$29="A/L",G$29="B/OFF",G$29="B/EDO")textNO
G25:T28Expression=OR(G$24="PHC",G$24="LSL",G$24="A/L",G$24="B/OFF",G$24="B/EDO")textNO
G20:T23Expression=OR(G$19="PHC",G$19="LSL",G$19="A/L",G$19="B/OFF",G$19="B/EDO")textNO
G15:T18Expression=OR(G$14="PHC",G$14="LSL",G$14="A/L",G$14="B/OFF",G$14="B/EDO")textNO
G10:T13Expression=OR(G$9="PHC",G$9="LSL",G$9="A/L",G$9="B/OFF",G$9="B/EDO")textNO
G29:T33Expression=OR(G$29="SDO",G$29="STFN",G$29="CDO",G$29="CTFN")textNO
G24:T28Expression=OR(G$24="SDO",G$24="STFN",G$24="CDO",G$24="CTFN")textNO
G19:T23Expression=OR(G$19="SDO",G$19="STFN",G$19="CDO",G$19="CTFN")textNO
G14:P18,Q14:T14Expression=OR(G$14="SDO",G$14="STFN",G$14="CDO",G$14="CTFN")textNO
Q9:T9,G9:P13Expression=OR(G$9="SDO",G$9="STFN",G$9="CDO",G$9="CTFN")textNO
H16:T16Expression=TIME(HOUR(H16),MINUTE(H16),0)+TIME(12,0,0)<TIME(HOUR(G17),MINUTE(G17),0)textNO
H16:T16Expression=AND(TIME(HOUR(G17),MINUTE(G17),0)<TIME(7,0,0),TIME(HOUR(G17),MINUTE(G17),0)+TIME(12,0,0)>TIME(HOUR(H16),MINUTE(H16),0))textNO
H31:T31Expression=TIME(HOUR(H31),MINUTE(H31),0)+TIME(12,0,0)<TIME(HOUR(G32),MINUTE(G32),0)textNO
H31:T31Expression=AND(TIME(HOUR(G32),MINUTE(G32),0)<TIME(7,0,0),TIME(HOUR(G32),MINUTE(G32),0)+TIME(12,0,0)>TIME(HOUR(H31),MINUTE(H31),0))textNO
H26:T26Expression=TIME(HOUR(H26),MINUTE(H26),0)+TIME(12,0,0)<TIME(HOUR(G27),MINUTE(G27),0)textNO
H26:T26Expression=AND(TIME(HOUR(G27),MINUTE(G27),0)<TIME(7,0,0),TIME(HOUR(G27),MINUTE(G27),0)+TIME(12,0,0)>TIME(HOUR(H26),MINUTE(H26),0))textNO
H21:T21Expression=TIME(HOUR(H21),MINUTE(H21),0)+TIME(12,0,0)<TIME(HOUR(G22),MINUTE(G22),0)textNO
H21:T21Expression=AND(TIME(HOUR(G22),MINUTE(G22),0)<TIME(7,0,0),TIME(HOUR(G22),MINUTE(G22),0)+TIME(12,0,0)>TIME(HOUR(H21),MINUTE(H21),0))textNO
G15:T15,G18:T18Cellcontains a blank value textNO
H16:T16Expression=TIME(HOUR(H16),MINUTE(H16),0)+TIME(12,0,0)<TIME(HOUR(G17),MINUTE(G17),0)textNO
H16:T16Expression=AND(TIME(HOUR(G17),MINUTE(G17),0)<TIME(7,0,0),TIME(HOUR(G17),MINUTE(G17),0)+TIME(12,0,0)>TIME(HOUR(H16),MINUTE(H16),0))textNO
H11:T11Expression=TIME(HOUR(H11),MINUTE(H11),0)+TIME(12,0,0)<TIME(HOUR(G12),MINUTE(G12),0)textNO
H11:T11Expression=AND(TIME(HOUR(G12),MINUTE(G12),0)<TIME(7,0,0),TIME(HOUR(G12),MINUTE(G12),0)+TIME(12,0,0)>TIME(HOUR(H11),MINUTE(H11),0))textNO
G4:T4Cellcontains an errortextNO
C29Expression=IF(C31<>"",TRUE)textNO
C24Expression=IF(C26<>"",TRUE)textNO
C19Expression=IF(C21<>"",TRUE)textNO
C14Expression=IF(C16<>"",TRUE)textNO
G32:T32Expression=IF(G$33<>"",TRUE)textNO
G31:T31Expression=IF(G$30<>"",TRUE)textNO
G27:T27Expression=IF(G$28<>"",TRUE)textNO
G26:T26Expression=IF(G$25<>"",TRUE)textNO
G22:T22Expression=IF(G$23<>"",TRUE)textNO
G21:T21Expression=IF(G$20<>"",TRUE)textNO
G12:T12Expression=IF(G$13<>"",TRUE)textNO
G11:T11Expression=IF(G$10<>"",TRUE)textNO
G17:T17Expression=IF(G$18<>"",TRUE)textNO
G16:T16Expression=IF(G$15<>"",TRUE)textNO
G9:T108Cell Valuecontains "EDO"textNO
G9:T108Cell Valuecontains "OFF"textNO
C1Cell Valuecontains "UNPUBLISHED"textNO
C1Cell Valuecontains "PUBLISHED"textNO
C9Expression=IF(C11<>"",TRUE)textNO
G10:T10,G13:T13Cellcontains a blank value textNO
Q15:T18Expression=OR(Q$14="SDO",Q$14="STFN",Q$14="CDO",Q$14="CTFN")textNO
Q10:T13Expression=OR(Q$9="SDO",Q$9="STFN",Q$9="CDO",Q$9="CTFN")textNO
G10:T10,G13:T13,G15:T15,G18:T18,G20:T20,G23:T23,G25:T25,G28:T28,G30:T30,G33:T33,G35:T35,G38:T38,G40:T40,G43:T43,G45:T45,G48:T48,G50:T50,G53:T53,G55:T55,G58:T58,G60:T60,G63:T63,G65:T65,G68:T68,G70:T70,G73:T73,G75:T75,G78:T78,G80:T80,G83:T83,G85:T85,G88:T88Celldoes not contain a blank value textNO
Cells with Data Validation
CellAllowCriteria
V7:AA8List=$G$2:$T$2
C13List=DATA!$X$3:$X$8
C33List=DATA!$X$3:$X$8
C28List=DATA!$X$3:$X$8
C23List=DATA!$X$3:$X$8
C18List=DATA!$X$3:$X$8
G19:T19List=DATA!$Z$3:$Z$31
G24:T24List=DATA!$Z$3:$Z$31
G29:T29List=DATA!$Z$3:$Z$31
G14:T14List=DATA!$Z$3:$Z$31
G9:T9List=DATA!$Z$3:$Z$31
C1:E2List=DATA!$AD$3:$AD$4
A3:B8List=DATA!$T$3:$T$69
C11:D11List=DATA!$V$3:$V$25
C16:D16List=DATA!$V$3:$V$25
C21:D21List=DATA!$V$3:$V$25
C26:D26List=DATA!$V$3:$V$25
C31:D31List=DATA!$V$3:$V$25
 

Attachments

  • Capture.PNG
    Capture.PNG
    185.7 KB · Views: 1

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Also well aware my conditional formatting is probably very cumbersome, but I'm not the best 😂
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Sheet code runtime error 1004
Replies
3
Views
103
MeaclH
M
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
159
MeaclH
M
L
  • Solved
Help with IF Formula for my Data.
Replies
5
Views
560
Lukma
L
G
  • Question
Combining / Reducing Multiple Formulas down to 1 Formula
Replies
3
Views
451
Bosquedeguate
Bosquedeguate
A
  • Question
Guys please help! Excel problem with index sort and filter function
Replies
2
Views
618
Adfassa
A

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top