How to insert a row without losing data in INDEX formulas
 

Hi--
I have some formulas (that I received help for here awhile back) that
basically take a set of numbers in multiple columns and rows, and
converts them all into one column with no blanks. The formulas work
fine, but the problem is they start in row 1 and I would like them to
start in Row 2. If I insert a row, I lose data (the first number in
the series does not show up anywhere). I can't figure out how to
adjust the formulas to start at row 2 but include all of the data.
Any suggestions?

Thanks,
Jason

The formula that puts all the numbers into one column is:

=IF(ROW()COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW()) ))

and then copied down the column. It starts in cell A1, but I would
like it to start in cell A2 without losing any data.

How to insert a row without losing data in INDEX formulas
 

I would like it to start in cell A2
=IF(ROW()COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW() )))

Try it like this:

=IF(ROWS(A$2:A2)COUNT(C:C),"",INDEX(B:B,SMALL(C:C ,ROWS(A$2:A2))))


--
Biff
Microsoft Excel MVP


"Jason" wrote in message
...
Hi--
I have some formulas (that I received help for here awhile back) that
basically take a set of numbers in multiple columns and rows, and
converts them all into one column with no blanks. The formulas work
fine, but the problem is they start in row 1 and I would like them to
start in Row 2. If I insert a row, I lose data (the first number in
the series does not show up anywhere). I can't figure out how to
adjust the formulas to start at row 2 but include all of the data.
Any suggestions?

Thanks,
Jason

The formula that puts all the numbers into one column is:

=IF(ROW()COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW()) ))

and then copied down the column. It starts in cell A1, but I would
like it to start in cell A2 without losing any data.