Return a String in a 7 Row Range Q

[Sean]

I have formula's that return text strings in A1:A7 (note these also
may return <blank>. So there maybe text in A3 and A6 only or a
combination of any of the 7.

How could I return in A1, the 1st string returned from A1:A7, the 2nd
string in A2 etc etc

In my example of A3:A6 above, the value essentially given in A3 would
appear in A1, the value appearing in A6 would appear in A2 and thus
nothing in A3:A7?

I hope I have not described this in a very confusing manner

Thanks

 

[Max]

One easy option to float it up in an adjacent col C
In B1: =IF(A1="","",ROW())
In C1: =IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))
Copy B1:C1 down to C7. Results in col C (Hide col B)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik

 

[Mike H]

A UDF maybe

Alt + f11 to open VB editor. Right clicl 'This Workboo' and insert module
and paste the code below in.

You call it like this
=popdown($A$1:$A$7,ROW(A1))

If dragged down it will return the populated cells in order and start
returning 'Error' when they run out.

Alternatively call it like this

=popdown($A$1:$A$7,4)
To return the 4th instance


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


Mike

 

[Sean]

Thanks Max / Mike

On your formula Max, I've tried a simple test and it works, but when I
apply it to my actual data I get a <blank> in a cell which I expect it
to return a string. I'll explain-

In D186 I have a formula =IF(AB$178="","","Sunday in - "&AB$178), this
produces a text string (I have <blank> returns in D180185)

Based on your Formula for "B" I have entered in H186
=IF(D186="","",ROW()). this produces 186 as a result (I have <blank>
returns in H180:H185)

Based on your formula for "C" I have entered in I180 =IF(ROW()>COUNT(H
$180:H$186),"",INDEX(D$180$186,SMALL(H$180:H$186,ROW()))), this
produces a <blank> in I180. I would have expected it to return the
text string that is displayed in D186.

has it anything to do with how I return my value in D186?

 

[Max]

ROW() is sensitive to the cell its placed in. If your data starts in other
than row1, you could replace ROW() with ROWS($1:1), viz:

In H180:
=IF(D180="","",ROWS($1:1))

In I180:
=IF(ROWS($1:1)>COUNT(H$180:H$186),"",INDEX(D$180$186,SMALL(H$180:H$186,ROWS($1:1))))

Copy H180:I180 down to I186, and it'll work fine, returns in I180:I186
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik

 

[Sean]

Max thanks for that, its absolutely ingenius

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik