# OzGrid

How to Index Across Multiple Tabs

Category: [Excel]  Demo Available

### 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.