=IF(ROWS(C$1:C1)<=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the range and compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)<=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is greater than the total
number of text entries in the range then the formula returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the first part of your formula,

=IF(ROWS(C$1:C1)<=COUNTIF(A$1:A$12,"*"),

why not just

=SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1))

what am I missing?

Mike

"T. Valko" wrote:

Assume you want the results starting in cell C1.

Try this array formula** entered in C1 and copied down until you get blanks:

=IF(ROWS(C$1:C1)<=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"")

This assumes your *REAL* data range does in fact start on row 1. If it
doesn't start on row 1 then we'll need to make a slight adjustment.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

As for your "complication", I don't think that can be done with a single
formula. If it can I'd love to see it!

--
Biff
Microsoft Excel MVP


"M" wrote:

I have a column that contains empty cells and text. I want to return the row
reference of the cells that contain text. For example€¦

A2 = empty cell
A3 = empty cell
A4 = 2L
A5 = empty cell
A6 = 2W
A7 = empty cell
A8 = 2L2W
A9 = empty cell
A10 = empty cell
A11 = 2L2W
A12 = empty cell

The formula would return 4. Thanks to Luke I have€¦

{=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))}

However I need to copy this formula down a column. The formula should return
4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I cant
sort the data as I have other equations in the sheet.

To add to this problem I need to repeat the row number if the original cell
contains 4 or more characters. So the formula should return 4 then 6 then 8
then 8 then 11 then 11 etc.

Thanks for your help.