I want to create a summary tab in excel that shows a section of 8 other tabs. The section is not always in the same location in each tab. I've been searching for hours trying to find the best way to do this, hopefully this post will help.
See the example file attached. I want a summary on the CTG tab that will pull the detail I highlighted in Blue from each of the other 8 tabs. I figured out how to search across multiple tabs with this array formula:
=VLOOKUP(A79,INDIRECT("'"&INDEX($M$2:$M$9,MATCH(TRUE,COUNTIF(INDIRECT("'"&$M$2:$M$9&"'!$A$1:A100"),A79)>0,0))&"'!$A$1:$B$100"),2,0)
This created the need for an identifier cell "x", but then I needed to find out how to make this formula search the next instance instead of repeating the same thing over. Having different identifiers is not possible and the number of "projects" per tab are always changing.
I'm not fixed on this equation if there is an easier way to do this. Any help is greatly appreciated.
Bookmarks