Index multiple sheets

Grawill

New member
Joined
Jan 27, 2014
Messages
3
Reaction score
0
Points
0
File attached.

I've used similar indexing formulas in the past, however it has always been a single worksheet. This time I am looking to index all worksheets and create a list of projects that are not 100% complete (H2:H1800 value < 100). I've spent a good bit of time trying this on my own and have finally grabbed the proverbial white flag.


I have worksheets labeled TAMP21 > TAMP38 all formatted exactly the same. I would like to search all worksheets (A1:H1800) and any value less then 100% in H1:H1800, I would like to copy the that row (A-H) to the summary tab. Here's what I have entered in A2 of my summary tab using CTRL + SHFT + ENTER to get the {} around the formula... it provides no errors and I do get some data, but the first result appears to repeat itself with the exception of column A.

Code:
[COLOR=#333333]=IF(ISERROR(INDEX(DATA!$A$1:$H$1800,SMALL(IF(DATA!$H$1:$H$1800<100,ROW(DATA!$A$1:$A$1800)),ROW(1:1)),1)),"",INDEX(DATA!$A$1:$H$1800,SMALL(IF(DATA!$H$1:$H$1800<100,ROW(DATA!$A$1:$A$1800)),ROW(1:1)),1))[/COLOR]




Thanks for the time and effort you may put into this for me.
Ted.
 

Attachments

  • Projects Tracking.xlsx
    315.9 KB · Views: 19
In I2 of the Data sheet enter formula:

=IF(H2<>"",IF(H2+0<100,COUNT(I$1:I1)+1,""),"")

that formula identifies and numbers the matches to the logical check.

copied all the way down.

Then in A2 of your Summary sheet enter:

=IFERROR(INDEX(DATA!A:A,MATCH(ROWS($A$2:$A2),DATA!$I:$I,0)),"")

copied across and down.

Also, please see here about Crossposting and why it is frowned upon: http://www.excelguru.ca/content.php?184

Please post all crosspost links
 
Last edited:
Thanks for the assistance. It worked perfectly!

I've taking your advice and added the link accordingly to my original post, however, because I'm a new person to this forum, I do not have the ability to post any links. While I understand the requirement of a post limit to avoid spam bots, I fear that very same limitation has set me up for failure. I'm unable to live up to the common courtesy of posting a link back to my original post on another forum.

I am very thankful to have people willing to assist strangers with some complex Excel formulas. It is much appreciated.

Ted.
 
Back
Top