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