Help Please! Question on using IF(ISERROR) to remove #NUM! errors

[LckySevan1]

I have the following formula, but need to remove the #NUM! errors in my spreadsheet. I've attempted to use the IF(ISERROR) function, but I can't seem to figure out where I'm going wrong. Any assistance would be greatly appreciated.


=INDEX('MASTER Tracking Sheet'!F$10:F$50, SMALL(IF($K$11='MASTER Tracking Sheet'!$A$10:$A$50, ROW('MASTER Tracking Sheet'!$A$10:$A$50)-MIN(ROW('MASTER Tracking Sheet'!$A$10:$A$50))+1, ""), ROW(A1)))

 

[vletm]

Some questions ...
1) What would be INDEX's row_num?
2) What would be 'SMALL's array? SMALL(array,k)
3) When would IF give 'True'? ... 'False' gives "".
'True' needs that $K$11='MASTER Tracking Sheet'!$A$10:$A$50
4) Have You tried to solve You 'formula' like this; smaller parts?

 

[LckySevan1]

Yes, perhaps I should give some greater context to the problem I am trying to solve. I have around 100 forms that I would like to pre-populate data fields from a Master tracking sheet where all the information is stored.

I am attempting to have the cells on the individual forms, find the reference cell on the master sheet, and then return the values in that same row from cells F10 through W10. I can get the values to show up using the formula above, but it may be that the formula is overly complicated. Nevertheless it works. There are simple SUM calculations in the individual forms however, and when the #NUM! error shows up (when all records have been returned) it ruins the calculation. I'm just attempting to get the errors to disappear.

 

[Monty]

LckySevan1

Wrap it all in an IFERROR statement.

=IFERROR(YourFormula,0)

 

[Khalid NGO]

Hi,

You can add IFERROR function to your formula as advised above, or you can replace SUM(Your_range) formula with:

=SUM(SUMIF(Your_range,{">0","<0"}))

It will ignore the errors.

Regards,