IFERROR problem

Hello, here is the problem I am facing:

I have a worksheet with 2500 rows of data called MaterialsIndex, with the last column being a $ amount stemming from a calculation based on row contents. After the last column of data, I have =IF(J12>0,K11+1,K11) in the top row's cell, copied all the way to the bottom of the data.

In a separate sheet that summarizes the data, I have

=IFERROR(INDEX(MaterialsIndex!$A$2:$J$10,MATCH(ROW(J1),MaterialsIndex!$K$2:$K$10,0),COLUMN(J1)),"")

Copied across the columns, and then copied down the rows. The objective is to dump the contents of the rows from the Data sheet in to the summary sheet if the contents of the last cell > 0.

Problem: It only works for 8 rows, afterward all cells in the summary are blank despite having the IFERROR formula. WHy does it stop working?!

Any help is appreciated....thanks!
John

On the last working cell / row, the second evaluation reads:

=IFERROR(INDEX(MaterialsIndex!$A$2:$J$10,MATCH(7,MaterialsIndex!$K$2:$K$10,0),COLUMN(J7))"")

And the third evaluation reads:

=IFERROR(INDEX(MaterialsIndex!$A$2:$J$10,9,COLUMN(J7))"")



On the first cell / row that doesn't work, the second evaluation reads:

=IFERROR(INDEX(MaterialsIndex!$A$2:$J$10,MATCH(8,MaterialsIndex!$K$2:$K$10,0),COLUMN(J8))"")

And the third reads:

=IFERROR(INDEX(MaterialsIndex!$A$2:$J$10,#NA,COLUMN(J8))"")


I don't know why the #NA as it is in the cell it needs to be in. Any ideas?
thanks,
John

I am stumped. The spreadsheet is attached, the two far right worksheets are the ones in question - Materials Summary, and Materials Index. The sheet 'Tubing' is the one I've been testing with and again can't make it past 8 rows. Any assistance is appreciated - thanks a lot!!
John

It's failing because of this part
MATCH(ROW(B1),MaterialsIndex!$K$2:$K$10,0)
You have that in B4
As you fill down, it changes to
MATCH(ROW(B2),MaterialsIndex!$K$2:$K$10,0)
MATCH(ROW(B3),MaterialsIndex!$K$2:$K$10,0)
MATCH(ROW(B4),MaterialsIndex!$K$2:$K$10,0)
etc...

When you get to
MATCH(ROW(B8),MaterialsIndex!$K$2:$K$10,0)
ROW(B8) = 8
So it's
MATCH(8,MaterialsIndex!$K$2:$K$10,0)

The number 8 does not exist in MaterialsIndex!$K$2:$K$10
So therefore the match returns #N/A
And the IFERROR captures that and returns ""


I think you just need to extend your ranges beyond row 10
=IFERROR(INDEX(MaterialsIndex!$A$2:$J$1000,MATCH(ROW(B1),MaterialsIndex!$K$2:$K$1000,0),COLUMN(B1)),"")



But looking over the formula some more...
Why not just put this in MaterialSummary B4
=MaterialsIndex!B3
And fill down/right

thanks a lot - the IFERROR works. I only want data if column J in MaterialsIndex has a value >0.

thanks again, and if you have a more direct and reliable formula I welcome the feedback.
cheers,
John

Originally Posted by johnhasaquestion

I only want data if column J in MaterialsIndex has a value >0.

In MaterialsSummary A4 and filled down/right as far as needed..

=IF(MaterialsIndex!$J3>0,MaterialsIndex!A3,"")