IF(ISERROR?? Help with hiding #NUM!

[charmz097]

I am using this
formula:=INDEX('Raw-MasterList'!$A$1:$H$1511,SMALL(IF('Raw-MasterList'!$G$3:$G$1511='Common&Tail ODIs'!$A3,ROW('Raw-MasterList'!$G$3:$G$1511)),V$2),2)
to pull multiple pieces of information from one part of a spreadsheet to
another
Some of my rows have more items than others so the #NUM! error appears, I
tried to get rid of it by adding IS(ERROR to the formula like so:
=IF(ISERROR(INDEX('Raw-MasterList'!$A$1:$H$1511,SMALL(IF('Raw-MasterList'!$G$3:$G$1511='Common&Tail
ODIs'!$A3,ROW('Raw-MasterList'!$G$3:$G$1511)),V$2),2)),"",INDEX('Raw-MasterList'!$A$1:$H$1511,SMALL(IF('Raw-MasterList'!$G$3:$G$1511='Common&Tail
ODIs'!$A3,ROW('Raw-MasterList'!$G$3:$G$1511)),V$2),2))

For some reason this isn't working the way it should, it is causing all
cells to become blank, even if there is no error, and in one column of cells
it creates data where none should be

Hope this makes sense, any help is appreciated!

 

[Imonit]

Hello there.

The best advice I can give you is to try and not to make your formulas
so complicated. Break it down.

1) Get rid of the iserror function from your main formula. Get it to
a state of working, while disregarding the error values or messages
that will follow it.

2) Now , move this formula to an "off screen" area (somewhere where no
one can see it or it will not be printed). If you are working with
your data in rows, then keep them aligned with the rest of the
information so that all rows are essentially one record.

3) Now you can make references to your formulas using additional
formulas.

For example : If your big huge formula was MOVED to say Z12 then in
Cell F12 (Where you actually want your values to appear) now you can
do this

=if(iserror(Z12),0,Z12)

The Zero will appear if there is an error (or you can make it Blank by
substituting the 0 for "") and if not an error then it will refer to
your actual huge formula result.

Anyhow, hope that gives you something to work with!

-Imonit

 

[charmz097]

Thanks for the tips, I didn't actually write the formula hence my problems
with it, I'll try to do what you said, hopefully it helps

 

[T. Valko]

Use a separate cell and enter this formula:

=COUNTIF('Raw-MasterList'!$G$3:$G$1511,'Common&Tail ODIs'!$A3)

Assume that formula is entered in cell B1.

Then add this to the beginning of your formula:

=IF(ROWS(A$1:A1)>B$1,"",your_formula_here))

Are you *sure* your current formula works properly?

This expression:

ROW('Raw-MasterList'!$G$3:$G$1511)

Will cause the INDEX to miss the first 2 rows of data.

It should be written as:

ROW('Raw-MasterList'!$G$3:$G$1511)-MIN(ROW('Raw-MasterList'!$G$3:$G$1511))+1

You can even shorten it by removing the sheet name since that is not
relevant to the calculation:

ROW($G$3:$G$1511)-MIN(ROW($G$3:$G$1511))+1

Or, if that might lead to confusion use defined named ranges and it will
shorten the entire formula considerably and make it easier to read.

 

[charmz097]

Still no luck with it, thanks for the help though, I think the problem is
somewhere within the original formula...

Thanks for the replies!

 

[T. Valko]

What's in V2?