How to Index Across Multiple Tabs

Requirement:

The user has a workbook that contains a list of employees and dates of occurrences for each employee, indicated by a 1 in the cell under the date. The year is split across two tabs. The user would like to have a summary page that brings in only the dates of occurrences for each employee based on a date range (entered start and end dates).

Solution:

Try this Array* formula in C4 of 'Overview'

=IFERROR(IFERROR(INDEX('Jan - June'!$C$3:$I$3,SMALL(IF(('Jan - June'!$C$3:$I$3>=$E$1)*(('Jan - June'!$C$3:$I$3<=$H$1)*('Jan - June'!$C4:$I4=1)),COLUMN('Jan - June'!$C$3:$I$3)-COLUMN('Jan - June'!$C$3)+1),COLUMNS($C4:C4))),INDEX('Jul - Dec'!$C$3:$I$3,SMALL(IF(('Jul - Dec'!$C$3:$I$3>=$E$1)*(('Jul - Dec'!$C$3:$I$3<=$H$1)*('Jul - Dec'!$C4:$I4=1)),COLUMN('Jul - Dec'!$C$3:$I$3)-COLUMN('Jul - Dec'!$C$3)+1),COLUMNS($C4:C4)-$B4+COUNTIFS('Jan - June'!$C$3:$I$3,">"&$E$1,'Jan - June'!$C$3:$I$3,"<="&$H$1,'Jan - June'!$C4:$I4,1)))),"")

Then copy across and down the table.

Obtained from the OzGrid Help Forum.

Solution provided by NBVC.