How can I create a list that skips zero values?

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.

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

>-----Original Message-----
>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.
>.
>

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" wrote:

> 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
>
> >-----Original Message-----
> >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.
> >.
> >
>

Hi!

If there are 100 rows, say F2:F101:

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

>Also - what is the reference to column A about?

>> Note: ROW(A$1:A$19) refers to the size of the range Feb
>>05!F$2:F$20.

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

>-----Original Message-----
>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" wrote:
>
>> 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
>>
>> >-----Original Message-----
>> >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.
>> >.
>> >
>>
>.
>