Roll up rows into another tab for annual leave calendar.

[Fluffy Stockings]

Hi all,


I've set up a leave calendar where we add the staff requests row by row. To the right of this there is column for each day of the year. In each of these cells there is a calculation to work out if that day for that person is a true holiday (populates the cell with data from another cell e.g. J6 "Leave PM"/J7 "Leave") or if it's a non-requested day/weekend/bank holiday (leave blank).


This works really well but unfortunately as with any leave request system multiple people can request multiple days on multiple occasions as shown in this spreadsheet. Person 1 has requested 3 holidays (3 rows), Person 2 has requested 2 holidays (2 rows) and Person 3 has requested 1 holiday (1 row).


Is there a way to roll these up on another tab of the spreadsheet which I'll split into months so each person only has 1 row per tab but the row is populated from all rows in the master year tab?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	01/01/2019
2	Holidays	01/01/2019	19/04/2019	22/04/2019	06/05/2019	27/05/2019	26/08/2019	25/12/2019	26/12/2019
3	Days in Year	365
4
5	Dates in Month		Day	Holiday		Name	Start Date	End Date	Number of days	Holiday Type	01/01/2019	02/01/2019	03/01/2019	04/01/2019
6	01/01/2019		Tue	TRUE		Person 1	01/01/2019	14/01/2019	14	Leave		Leave	Leave	Leave
7	02/01/2019		Wed	FALSE		Person 2	02/01/2019	03/01/2019	2	Leave		Leave	Leave
8	03/01/2019		Thu	FALSE		Person 3	06/01/2019	06/01/2019	1	Leave PM
9	04/01/2019		Fri	FALSE		Person 1	12/03/2019	17/03/2019	6	Leave
10	05/01/2019		Sat	TRUE		Person 1	15/07/2019	21/07/2019	7	Leave
11	06/01/2019		Sun	TRUE		Person 2	01/03/2019	01/03/2019	1	Leave AM
12	07/01/2019		Mon	FALSE
2019

Cell Formulas
Range	Formula
C6	=A6
C7	=A7
C8	=A8
C9	=A9
C10	=A10
C11	=A11
C12	=A12
D6	=NOT(NETWORKDAYS.INTL(A6,A6,1,$B$2:$G$2))
D7	=NOT(NETWORKDAYS.INTL(A7,A7,1,$B$2:$G$2))
D8	=NOT(NETWORKDAYS.INTL(A8,A8,1,$B$2:$G$2))
D9	=NOT(NETWORKDAYS.INTL(A9,A9,1,$B$2:$G$2))
D10	=NOT(NETWORKDAYS.INTL(A10,A10,1,$B$2:$G$2))
D11	=NOT(NETWORKDAYS.INTL(A11,A11,1,$B$2:$G$2))
D12	=NOT(NETWORKDAYS.INTL(A12,A12,1,$B$2:$G$2))
K6	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G6,K$5<=$H6),$J6,"")
K7	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G7,K$5<=$H7),$J7,"")
K8	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G8,K$5<=$H8),$J8,"")
K9	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G9,K$5<=$H9),$J9,"")
K10	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G10,K$5<=$H10),$J10,"")
K11	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G11,K$5<=$H11),$J11,"")
K12	=IF(AND(NETWORKDAYS.INTL(K$5,K$5,1,$B$2:$I$2),K$5>=$G12,K$5<=$H12),$J12,"")
L6	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G6,L$5<=$H6),$J6,"")
L7	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G7,L$5<=$H7),$J7,"")
L8	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G8,L$5<=$H8),$J8,"")
L9	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G9,L$5<=$H9),$J9,"")
L10	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G10,L$5<=$H10),$J10,"")
L11	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G11,L$5<=$H11),$J11,"")
L12	=IF(AND(NETWORKDAYS.INTL(L$5,L$5,1,$B$2:$I$2),L$5>=$G12,L$5<=$H12),$J12,"")
M6	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G6,M$5<=$H6),$J6,"")
M7	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G7,M$5<=$H7),$J7,"")
M8	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G8,M$5<=$H8),$J8,"")
M9	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G9,M$5<=$H9),$J9,"")
M10	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G10,M$5<=$H10),$J10,"")
M11	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G11,M$5<=$H11),$J11,"")
M12	=IF(AND(NETWORKDAYS.INTL(M$5,M$5,1,$B$2:$I$2),M$5>=$G12,M$5<=$H12),$J12,"")
N6	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G6,N$5<=$H6),$J6,"")
N7	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G7,N$5<=$H7),$J7,"")
N8	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G8,N$5<=$H8),$J8,"")
N9	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G9,N$5<=$H9),$J9,"")
N10	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G10,N$5<=$H10),$J10,"")
N11	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G11,N$5<=$H11),$J11,"")
N12	=IF(AND(NETWORKDAYS.INTL(N$5,N$5,1,$B$2:$I$2),N$5>=$G12,N$5<=$H12),$J12,"")
I6	=H6-G6+1
I7	=H7-G7+1
I8	=H8-G8+1
I9	=H9-G9+1
I10	=H10-G10+1
I11	=H11-G11+1
A6	=IF(ROWS(A$6:A6)>$B$3,"",$B$1+ROWS(A$6:A6)-1)
A7	=IF(ROWS(A$6:A7)>$B$3,"",$B$1+ROWS(A$6:A7)-1)
A8	=IF(ROWS(A$6:A8)>$B$3,"",$B$1+ROWS(A$6:A8)-1)
A9	=IF(ROWS(A$6:A9)>$B$3,"",$B$1+ROWS(A$6:A9)-1)
A10	=IF(ROWS(A$6:A10)>$B$3,"",$B$1+ROWS(A$6:A10)-1)
A11	=IF(ROWS(A$6:A11)>$B$3,"",$B$1+ROWS(A$6:A11)-1)
A12	=IF(ROWS(A$6:A12)>$B$3,"",$B$1+ROWS(A$6:A12)-1)

 

[Rijnsent]

Hi Fluffy,

what I would do to roll it up is to ignore the holidays for a first aggregation:
-I made a grid on a sheet with the dates in row 1 and the people in column 1 (both headers)
-so the first formula goes in B2 (your data is on a sheet named SHT): ={IFERROR(INDEX(SHT!$K$6:$K$11,MATCH(1,($A2=SHT!$G$6:$G$11)*(B$1>=SHT!$H$6:$H$11)*(B$1<=SHT!$I$6:$I$11),0)),"")}
-note it's an array formula, so use CTRL+SHIFT+ENTER if you enter the formula
-the base/inspiration of the formula is here: https://exceljet.net/formula/index-and-match-with-multiple-criteria

Hope that helps you moving on.
Cheers,
Koen