Hi,
So im currently recreating a new warehouse rota, its coming along really well and now at the stage where i need to improve and speed up the sheet. Any advise, tips etc are very welcome as im looking to learn from this to improve going forward. Thanks
The first code is
A3 = Name
Teams BHR4 = 080080000080080080080000000800800800800800000000080080080080000800800800800000000800080080080000080080080000000800800800800800000000080080080080000800800000000800800800
This will generate ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000
The second code takes the first part and breaks down to what is needed. Please note that this information is used between the sheet this is based from and also another sheet.
A9 = Name
BE5 = Date
Shifts Ref = ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000
So im currently recreating a new warehouse rota, its coming along really well and now at the stage where i need to improve and speed up the sheet. Any advise, tips etc are very welcome as im looking to learn from this to improve going forward. Thanks
The first code is
A3 = Name
Teams BHR4 = 080080000080080080080000000800800800800800000000080080080080000800800800800000000800080080080000080080080000000800800800800800000000080080080080000800800000000800800800
Excel Formula:
=IF($A3<>"","ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,B$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&IF(LEFT(Jan!FC6,3)="OVT",Jan!FC6,"OVT000")&IF(LEFT(Jan!FC6,3)="SSI",Jan!FC6,"SSI000")&IF(LEFT(Jan!FC6,3)="SSO",Jan!FC6,"SSO000")&IF(LEFT(Jan!FC6,3)="SDS",Jan!FC6,"SDS000")&IF(LEFT(Jan!FC6,3)="HOL",Jan!FC6,"HOL000")&IF(LEFT(Jan!FC6,3)="LID",Jan!FC6,"LID000")&IF(LEFT(Jan!FC6,3)="UNP",Jan!FC6,"UNP000")&IF(LEFT(Jan!FC6,3)="FLD",Jan!FC6,"FLD000")&IF(LEFT(Jan!FC6,3)="MAT",Jan!FC6,"MAT000")&IF(LEFT(Jan!FC6,3)="LIS",Jan!FC6,"LIS000")&IF(LEFT(Jan!FC6,3)="CBR",Jan!FC6,"CBR000")&IF(LEFT(Jan!FC6,3)="ABS",Jan!FC6,"ABS000"),"")
This will generate ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000
The second code takes the first part and breaks down to what is needed. Please note that this information is used between the sheet this is based from and also another sheet.
A9 = Name
BE5 = Date
Shifts Ref = ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000
Excel Formula:
=IFERROR(IF($A9<>"",
IF(BE$5="","NA",
IF(BE$5<Teams!$C4,"NE",
IF(AND(Teams!$D4>0,BE$5>Teams!$D4),"NE",
IF(IFERROR(NOT(VLOOKUP(BE$5,L_HOLS_SHIFT,2,FALSE)=0),FALSE),"CH",
IF(LEN(Teams!$BHR4)>0,
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),10,1)="8","OVT",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),16,1)="8","SSI",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),22,1)="8","SSO",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),34,1)="8","HOL",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),40,1)="8","LID",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),46,1)="8","UNP",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),52,1)="8","FLD",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),58,1)="8","MAT",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),64,1)="8","LIS",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),70,1)="8","CBR",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),76,1)="8","ABS",
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),4,1)+
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),10,1)+
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),4,1)="0",
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),28,1),"0")-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),16,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),22,1)-
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),4,1)>"0",
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),28,1),"0")-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),34,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),40,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),46,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),52,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),58,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),64,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),70,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),76,1)
))))))))))),""))))),""),"")