Believe Bob's array works, but you probably didn't array-enter it correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies, then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col A,
say down to D100? Hide away col D. Col C will return the required results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
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?