=INDEX(A:A,SMALL(IF($A$2:$A$100<"",ROW($A$2:$A$10 0)),ROW(A1)))

Enter the formula using Ctrl+Shift+Enter; to return the 1st value in ColA.
Copy/drag the formula down to get the subsequent values

If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

A B
1 apples
2 apples bananas
3 pears
4
5 bananas
6
7 pears

Can you write an array formula to make the above work. I sorta understand
but not quite. THANX

"Jacob Skaria" wrote:

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=INDEX(P:P,SMALL(IF($P$2:$P$100<"",ROW($P$2:$P$10 0)),ROW(A1)))


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

I want to check a cell for content and if there is none go to the next cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3