Amendment to a formula?

[Kirstym1918]

Hi everyone,

A little help is required if anyone is willing

I have this formula below which pulls staff hours from each weekly tab as the hours are input daily which is in the first two VLOOKUP formulas, if no hours are found for that particular person then it reverts to the INDEX MATCH formula which pulls the data from their rota i.e Holiday, Rest day, Overtime (This then compiles the below which by the end of the week is filled with hours worked). It works great except, I would like it to look at the rota first and if the data match in the rota equals "overtime", I want it to use this rather than pull their hours through from the VLOOKUP formula. Can anyone offer any help of how I should do that at the beginning of the formula?

=IF($C44<>"",IFERROR(VLOOKUP('HB Overtime'!$C44,Sun!$C$6:$F$501,4,FALSE),IFERROR(VLOOKUP($C44,Sun!$H$6:$K$501,4,FALSE),IFERROR(INDEX(HB_Rota'!$D$4:$NE$500,MATCH($C44,HB_Rota'!$C$4:$C$500,0),MATCH(D$2,HB_Rota'!$D$1:$NE$1,0)),""))),"")

 

[hernantorres23]

Hi, I'm very interested in helping you, always I have passionate in Payroll and Budgets topics, but please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares

Thanks a lot

 

[Kirstym1918]

Thanks very much, sorry it took a while, I've been trying to condense all three workbooks that it pulls from so that it is on one sheet so you can understand where the formulas are pulling the information from

 

[Kirstym1918]

Please see below, I have highlighted the cells which show the issue, for example JoeBloggs2 on Tuesday 18th Feb would have been on rest day but decided to work instead therefore his working hours have been input by our office staff in K19:N19, I don't want this to be calculated as hours in Cell E5, I want it to revert back to the rota data (Data in cell N5) whenever W-5th is recorded on the rota. I hope this makes sense?

I've dragged data from three separate workbooks below to simplify it for you, Formulas are usually linked via sharepoint files but due to workplace data I've had to replicate it

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		16/02/2020	17/02/2020	18/02/2020	19/02/2020	20/02/2020	21/02/2020	22/02/2020		Employee Name	16/02/2020	17/02/2020	18/02/2020	19/02/2020	20/02/2020	21/02/2020	22/02/2020
3	Employee Name	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY		Name	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
4	Joe Bloggs1	R	R	R						Joe Bloggs1	R	R	R	W	W	W	W
5	Joe Bloggs2	R	R	9.5						Joe Bloggs2	R	R	W-5th	W	W	W	W
6	Joe Bloggs3	R	10.5	10.5						Joe Bloggs3	R	W	W	R	R	W	W
7	Joe Bloggs4	R	9.5	9.5						Joe Bloggs4	R	W	W	W	W	R	R
8	Joe Bloggs5	R	6.5	6.5						Joe Bloggs5	R	W	W	W	W	R	R
9	Joe Bloggs6	R	10.5	10.5						Joe Bloggs6	R	W	W	R	R	W	W
10
11
12
13	Sunday Tab				Monday Tab					Tuesday Tab
14	Employee Name	Start	End	Hours	Name	Start	End	Hours		Name	Start	End	Hours
15					Joe Bloggs3	05:30	16:00	10.5		Joe Bloggs3	05:30	16:00	10.5
16					Joe Bloggs4	06:30	16:00	9.5		Joe Bloggs4	06:30	16:00	9.5
17					Joe Bloggs5	07:30	14:00	6.5		Joe Bloggs5	07:30	14:00	6.5
18					Joe Bloggs6	08:30	19:00	10.5		Joe Bloggs6	08:30	19:00	10.5
19										Joe Bloggs2	08:30	18:00	9.5
20
21
22
23
Overtime

Cell Formulas
Range	Formula
C4:C10	C4	=IF($B4<>"",IFERROR(VLOOKUP($B4,$B$15:$E$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B4,$K$4:$K$10,0),MATCH(C$2,$L$2:$R$2,0)),"")),"")
D4:D10	D4	=IF($B4<>"",IFERROR(VLOOKUP($B4,$F$15:$I$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B4,$K$4:$K$10,0),MATCH(D$2,$L$2:$R$2,0)),"")),"")
E4:E10	E4	=IF($B4<>"",IFERROR(VLOOKUP($B4,$K$15:$N$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B4,$K$4:$K$10,0),MATCH(E$2,$L$2:$R$2,0)),"")),"")
I15:I22,N15:N22	I15	=IF(H15<>"",ROUND(((H15-G15+(H15<G15)-$A$1)*24)/0.25,0)*0.25,"")

 

[hernantorres23]

Hi, an idea for you

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		16-Feb-2020	17-Feb-2020	18-Feb-2020	19-Feb-2020	20-Feb-2020	21-Feb-2020	22-Feb-2020		Employee Name	16-Feb-2020	17-Feb-2020	18-Feb-2020	19-Feb-2020	20-Feb-2020	21-Feb-2020	22-Feb-2020
3	Employee Name	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY		Name	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
4	Joe Bloggs1	R	R	R						Joe Bloggs1	R	R	R	W	W	W	W
5	Joe Bloggs2	R	R							Joe Bloggs2	R	R	W-5th	W	W	W	W
6	Joe Bloggs3	R	10.5	10.5						Joe Bloggs3	R	W	W	R	R	W	W
7	Joe Bloggs4	R	9.5	9.5						Joe Bloggs4	R	W	W	W	W	R	R
8	Joe Bloggs5	R	6.5							Joe Bloggs5	R	W	W-5th	W	W	R	R
9	Joe Bloggs6	R	10.5	10.5						Joe Bloggs6	R	W	W	R	R	W	W
10
11
12
13	Sunday Tab				Monday Tab					Tuesday Tab
14	Employee Name	Start	End	Hours	Name	Start	End	Hours		Name	Start	End	Hours
15					Joe Bloggs3	5:30	16:00	10.5		Joe Bloggs3	5:30	16:00	10.5
16					Joe Bloggs4	6:30	16:00	9.5		Joe Bloggs4	6:30	16:00	9.5
17					Joe Bloggs5	7:30	14:00	6.5		Joe Bloggs5	7:30	14:00	6.5
18					Joe Bloggs6	8:30	19:00	10.5		Joe Bloggs6	8:30	19:00	10.5
19										Joe Bloggs2	8:30	18:00	9.5
20
21
22
23
Sheet6

Cell Formulas
Range	Formula
C4:C10	C4	=IF($B4<>"",IFERROR(VLOOKUP($B4,$B$15:$E$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B4,$K$4:$K$10,0),MATCH(C$2,$L$2:$R$2,0)),"")),"")
D4:D10	D4	=IF($B4<>"",IFERROR(VLOOKUP($B4,$F$15:$I$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B4,$K$4:$K$10,0),MATCH(D$2,$L$2:$R$2,0)),"")),"")
E4, E10	E4	=IF($B4<>"",IFERROR(VLOOKUP($B4,$K$15:$N$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B4,$K$4:$K$10,0),MATCH(E$2,$L$2:$R$2,0)),"")),"")
E5:E9	E5	=IF(AND($B5<>"",N5<>"W-5th"),IFERROR(VLOOKUP($B5,$K$15:$N$22,4,FALSE),IFERROR(INDEX($L$4:$R$10,MATCH($B5,$K$4:$K$10,0),MATCH(E$2,$L$2:$R$2,0)),"")),"")
I15:I22, N15:N22	I15	=IF(H15<>"",ROUND(((H15-G15+(H15<G15)-$A$1)*24)/0.25,0)*0.25,"")