I feel that the following method lends itself better to generating multiple weeks:
On the Conference 1 and 2 sheets populate AP37:AX45 using: =IF(AP3="BYE","",MIN(MID(AP3,6,1)+0,RIGHT(AP3,1))&" "&MAX(MID(AP3,6,1)+0,RIGHT(AP3,1)))
Populate AP48:AX56 using: =IF(AND(AP37<>"",COUNTIFS($AP37:AP37,AP37)=1),AP3,"")
On the Weekly Schedule sheet rows are populated using:
Formula:
=IF(ROWS(A$1:A1)<=4,INDEX('Conference 1'!$AP$48:$AX$56,MATCH(B$4,'Conference 1'!$AO$48:$AO$56,0),AGGREGATE(15,6,(COLUMN('Conference 1'!$AP$48:$AX$56)-COLUMN('Conference 1'!$AO$48))/('Conference 1'!$AP$48:$AX$56<>"")/('Conference 1'!$AO$48:$AO$56=B$4),ROWS(A$1:A1))),INDEX('Conference 2'!$AP$48:$AX$56,MATCH(B$4,'Conference 2'!$AO$48:$AO$56,0),AGGREGATE(15,6,(COLUMN('Conference 2'!$AP$48:$AX$56)-COLUMN('Conference 2'!$AO$48))/('Conference 2'!$AP$48:$AX$56<>"")/('Conference 2'!$AO$48:$AO$56=B$4),ROWS(A$1:A1)-4)))
Let us know if you have any questions.
Bookmarks