i have a range which contains a series of values some are numbers some are
text and some are spaces. I want to create a copy of this list further up the
sheet but without the spaces.

I have tried using the lookup function and the lookup function with match
but always have trouble returning both numbers and text.

So I want cell C3 to return the first non empty cells value in range
C50:C150 and C4 to return the second non empty cell in the same range and so
on to a total of 15 values (c18).

any ideas ??