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 ??

Hi,

A UDF perhaps. Alt + F11 to open VB editor. Right click 'This workbook' and
paste the code below in on the right.

Call the code with this in C3

=getrange(C50:C150,ROW(A1))

Drag down.

Function GetRange(rng As Range, instance As Integer)
For Each c In rng
If c.Value < "" Then
instfound = instfound + 1
If instfound = instance Then GetRange = c.Value
End If
Next
If GetRange = "" Then GetRange = "Error"
End Function


Mike


"Atishoo" wrote:

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 ??

seems logical but not working! not sure why??

"Mike H" wrote:

Hi,

A UDF perhaps. Alt + F11 to open VB editor. Right click 'This workbook' and
paste the code below in on the right.

Call the code with this in C3

=getrange(C50:C150,ROW(A1))

Drag down.

Function GetRange(rng As Range, instance As Integer)
For Each c In rng
If c.Value < "" Then
instfound = instfound + 1
If instfound = instance Then GetRange = c.Value
End If
Next
If GetRange = "" Then GetRange = "Error"
End Function


Mike


"Atishoo" wrote:

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 ??