Results 1 to 3 of 3

Thread: Index multiple sheets

  1. #1

    Index multiple sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    =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))




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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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 by NBVC; 2014-01-27 at 04:33 PM.


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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •