Sub findgreaterthan()
J = 1
For i = 1 To 100
If IsNumeric(Cells(i, 1)) And Cells(i, 1) 0 Then
Cells(J, 3) = Cells(i, 2)
J = J + 1
End If
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds, shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I
need
it to keep moving down the A column, and then the next time it finds a
value
greater than zero, to show that corresponding text in cell C2, then keep
checking down and show the next one in cell C3 and so on. So at the end of
it
checking hundreds of values in column A, I might have seven or eight (Or
three, or fifty etc) cells at the top of column C filled in with text from
column B.

I hope that makes sense.....any ideas?

"Bob Phillips" wrote:

Put this in C1 and copy down

=IF(ISERROR(SMALL(IF($A$1:$A$100,ROW($A$1:$A$10)) ,ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$1 00,ROW($A$1:$A$10)),ROW($A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"dim" wrote in message
...
Hi, this one is a doosie! If anyone can help it'd be really great.

I have three columns of data....A1:A10, B1:B10 and C1:C5.

Column A is Numeric Data in each cell, Column B contains text in each
cell,
and I want my function to work on column C.

I want the fuction to check down along column A, from A1, until it
finds a
value greater than zero, at which time it will copy that A cell's
corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any
time a
value is entered into C1, then I want the function to copy the next
value
it
finds into C2 instead, and so on into C3 etc.

With this sheet, column B has about 100 text entries, but only 10 or so
of
those entries corresponding numbers in column A will be greater than 0.
I
want all those text entries organised at the top of column C for easy
referance, so putting a seperate function into each of 100 column C
cells
is
not an option.

Does anyone have any ideas?