Below is a possible solution to the challenge – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can contain a maximum of 20 projects and there are 7 days in a week and you can have a maximum of 20*7=140 rows)
=IFERROR(IF(INDIRECT("Day"&ROUNDUP(ROWS($1:1)/20,0)&"!"&ADDRESS(
ROUNDUP(MOD(ROWS($1:1)-1,20)+2,0),COLUMNS($A:A)))=0,"",INDIRECT("Day"&ROUNDUP(
ROWS($1:1)/20,0)&"!"&ADDRESS(ROUNDUP(MOD(ROWS($1:1)-1,20)+2,0),COLUMNS($A:A)))),"")
This will prepare a sheet contains all projects, PMs and Vendors.
Optionally – You can hide this sheet.
2. In the Consolidated Weekly Sheet, enter following formulas and drag down till rows 141.
A3: =IFERROR(INDEX(Projects!A$2:A$141, MATCH(0, IF(Projects!A$2:A$141<>"",
COUNTIF(A2:A$2, Projects!A$2:A$141)), 0)),"")
Note – Formula in A3 is an Array Formula. Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.
This formula generates unique listing of projects from the Projects Sheet.
B3: =VLOOKUP($A3,Projects!$A:$B,2,0)
C3:=VLOOKUP($A3,Projects!$A:$C,3,0)
D3: =IF($A3="","",IFERROR(INDEX(CHOOSE(MOD(COLUMNS($A:A)-1,2)+1,
INDIRECT("Day"&INDIRECT(ADDRESS(1,2+2*ROUNDUP(COLUMNS($A:A)/2,0)))
&"!$D$2:$D$20"),INDIRECT("Day"&INDIRECT(ADDRESS(1,2+2*ROUNDUP(
COLUMNS($A:A)/2,0)))&"!$E$2:$E$20")),INDEX(MATCH($A3&"##"&$B3&
"##"&$C3,INDIRECT("Day"&INDIRECT(ADDRESS(1,2+2*ROUNDUP(COLUMNS($A:A)/2,0)))
&"!$A$2:$A$20")&"##"&INDIRECT("Day"&INDIRECT(ADDRESS(1,2+2*ROUNDUP(
COLUMNS($A:A)/2,0)))&"!$B$2:$B$20")&"##"&INDIRECT("Day"&INDIRECT(ADDRESS(1,
2+2*ROUNDUP(COLUMNS($A:A)/2,0)))&"!$C$2:$C$20"),0),,)),""))
Formula of D3 should be dragged till column Q
The solution workbook can be downloaded from Solution – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet