M
M
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
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 can’t 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
(in column A) contains 4 or more characters. So the formula should return 4
then 6 then 8 then 8 then 11 then 11 etc.
I have the following two formulas that work but I can’t successfully combine
them
To return row numbers that contain text (Thanks to Biff)
=IF(ROWS(C$3:C3)<=COUNTIF(A$1:A$12,"*"),SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$3:C3)),"")
Logic test to either carry out the above or repeat the row number in the
cell above
=OR($C1=$C2,LEN(INDIRECT("A"&$C2))<3)
I hope this is clear
Thanks for your help.
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
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 can’t 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
(in column A) contains 4 or more characters. So the formula should return 4
then 6 then 8 then 8 then 11 then 11 etc.
I have the following two formulas that work but I can’t successfully combine
them
To return row numbers that contain text (Thanks to Biff)
=IF(ROWS(C$3:C3)<=COUNTIF(A$1:A$12,"*"),SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$3:C3)),"")
Logic test to either carry out the above or repeat the row number in the
cell above
=OR($C1=$C2,LEN(INDIRECT("A"&$C2))<3)
I hope this is clear
Thanks for your help.