How to Index Across Multiple Tabs

< Back to Search results

 Category: [Excel]  Demo Available 

How to Index Across Multiple Tabs




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




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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to obtain validation list values from 3 columns based on INDEXMATCH of value offset from cell

How to use Index/Countif based on multiple factors including range
How to use SUMIFS and include 'All' to Drop-down IndexMatch
How to use IndexMatch formula that ignores duplicates


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)