I would have to see the full original formula to tell you what's it doing
and how it's doing it.

I recommend not using that ROW(INDIRECT("1:"&ROWS(Data))) method. The basic
formula to extract the data is already fairly calculation intensive so you
want to do everything you can to make it as efficient as possible.


--
Biff
Microsoft Excel MVP


"Gene" wrote in message
...
You are right about the INDIRECT. I didn't copy correctly. I did in the
test however. My first thought was the formula format was incorrect.but
on
examination I realized the author was using the absence of the FALSE
statement in the IF as a means to skip the row in the result column. I
wondered if that was still available in 2007. The last statement in the
evaluation process in both my test and the downloaded worksheet was
IFERROR(33,""). I would expect 33 to be my result but in the downloaded
worksheet the result was the appropriate return????
--
GeneS


"T. Valko" wrote:

This expression is incorrect:

ROW("1:"&ROWS(Data))

You would need to wrap that inside the INDIRECT function like this:

ROW(INDIRECT("1:"&ROWS(Data)))

However, that's very inefficient.

Try it like this...

Assume you enter the first formula in cell C1.

Array entered**

=IFERROR(INDEX(Data,SMALL(IF(Data<"",ROW(Data)),R OWS(C$1:C1))-MIN(ROW(Data))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Gene" wrote in message
...
I got the following formula from a book which was accompanied by a CD. I
downloaded the example which did exactly what I wanted. When I
dluplicated
the example on a new workbook, however, it did not work.


{IFERROR(INDEX(Data,SMALL(IF(Data<"",ROW("1:"&ROW S(Data)))),ROW("1:"&ROWS(Data))))),"")}

Data Result My Result
33 33 33
-10 -10 33
20 33
20 15 33
etc. I think I have the latest updates for 2007 and the
book
was of course written some time ago. I evaluated the formulas and the
process was the same on both the download and my worksheet. Only the
result
was different. Can someone advise.?
--
GeneS