How can I create a list that skips zero values?

[S.K.S.]

I am trying to generate an "index" of sorts, for a series of spreadsheets.
This index is supposed to be a list of "hot items:" row entries for which the
user entered a one-word comment. Only about 10 percent of the rows have a
word in the "comment" column.

My formula for this index looks like this:

=IF(('Feb 05'!F2=0),goto 'Feb 05'!F3,('Feb 05'!F2))

(Where Feb 05 is the name of the first spreadsheet I need to index.)

"goto" is what I want the forumla to do: if the value in the "comment" cell
is null, then skip to the next row.

Any help would be much appreciated!

Thanks.

-S.K.S.

 

[Biff]

Hi!

So, what you want to do is to extract the "comments" to a
new list?

Assume the "comments" are in the range F2:F20.

Try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=INDEX('Feb 05'!F$2:F$20,SMALL(IF('Feb 05'!F$2:F$20<>"",ROW
(A$1:A$19)),ROW(1:1)))

Copy down until you get #NUM! errors meaning the data has
been exhausted.

Note: ROW(A$1:A$19) refers to the size of the range Feb 05!
F$2:F$20. You could build into the formula a method that
will automatically calculate the size of the range but
more detail would be needed to come up with a specific
suggestion.

Biff

 

[S.K.S.]

Thanks! That gets me closer.

However, when I change the addresses to reflect that there are 100 rows
(rather than 20) in the dbase, I get garbage.

Also - what is the reference to column A about?

Thanks.

-S.K.S.

 

[Biff]

Hi!

If there are 100 rows, say F2:F101:

=INDEX('Feb 05'!F$2:F$101,SMALL(IF('Feb 05'!

Also - what is the reference to column A about?

See if this explanation is easier to understand.

INDEX F2:F101 creates a virtual array that contains 100
items. F2:F101 is the physical location of those items on
the worksheet. In the virtual array F2 is in the first
position. F3 in the second, F4 in the third, etc..

Using ROW(A$1:A$100) is just a means of defining the size
of the virtual array. The references to column A have no
significance. ROW(A$1:A$100) evaluates to ROW(1:100)

When the condition of the IF statement is TRUE, the
formula returns the corresponding value in the virtual
array based on it's position, 1:100.

Biff