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

First thing that comes to my mind is two things.

Idea 1:
You're row counter is incrementing correctly, perhaps one of the quotation
marks is in wrong spot? Check to make sure both formulas match.

Idea 2:
Since results are only first line of data, your formula does not appear to
be operating as an array. Make sure formula is confirmed using
Ctrl+Shift+Enter, not just enter, and XL puts the curly brackets in itself.

Also, since your formula is returning a result, you must somehow have this
corrected, but the formula you posted is unbalanced in it's parathensis. By
this I mean, you have 2 more )'s than ('.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Gene" wrote:

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

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

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

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

I'll try to avoid the clause you identified but for the sake of showing you
I will duplicate it here. Incidentally, can you explain why the downloaded
version worked but I could not duplicate it.?

={IFERROR(INDEX(Data,SMALL(IF(Data<"",ROW(INDIREC T("1:"&ROWS(Data)))),Row(INDIRECT("1:"&ROWS(Data)) ))),"")}

I thought the exclusion of the FALSE portion of the IF statement was an
interesting technique. Can you comment on it?
--
GeneS


"T. Valko" wrote:

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

That formula is designed to be entered as a range array. For example, you
select a range of 10 cells like C1:C10 then you enter the formula in all the
cells at once. Is that how you entered it?

the exclusion of the FALSE portion of the IF statement
was an interesting technique. Can you comment on it?

You're only interested in the cells that meet the condition of <"" (not
equal to blank). So, we have the logical test:

IF(Data<"",ROW(INDIRECT("1:"&ROWS(Data))))

Where the condition is TRUE, the row numbers are passed to the SMALL
function. Since the value_if_false argument was omitted the default return
is the logical value FALSE. These are also passed to the SMALL function.We
might have an array like this being passed to the SMALL function:

1;FALSE;FALSE;4;5;FALSE

The SMALL function ignores the logical FALSE and calculates solely on the
numeric values.


--
Biff
Microsoft Excel MVP


"Gene" wrote in message
...
I'll try to avoid the clause you identified but for the sake of showing
you
I will duplicate it here. Incidentally, can you explain why the
downloaded
version worked but I could not duplicate it.?

={IFERROR(INDEX(Data,SMALL(IF(Data<"",ROW(INDIREC T("1:"&ROWS(Data)))),Row(INDIRECT("1:"&ROWS(Data)) ))),"")}

I thought the exclusion of the FALSE portion of the IF statement was an
interesting technique. Can you comment on it?
--
GeneS


"T. Valko" wrote:

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