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