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.
Thanks for the time and effort you may put into this for me.
Ted.
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.