only returning cells with Text in it

V

vennard

Hi,

I have a column that returns some text, if something else is true. Now
what I want to do is take only the cells in that column and list them
on another sheet.

The trick is, that ill need to do this with a frmula, since it will be
a hosted template and the user wont be able to use excel functions like
sort, etc.


so, if I have 10 cells and 3 of them have test in them, I want to show
only the text in those 3 cells in another sheet.

I hope that makes sense....

Thanks for your help....
 
D

Domenic

Assuming that Sheet1!A1:A10 contains your data, enter the following
formula in Sheet2!A1 and copy down:

=IF(ROWS($A$1:A1)<=COUNTIF(Sheet1!$A$1:$A$10,"?*"),INDEX(Sheet1!$A$1:$A$1
0,SMALL(IF(Sheet1!$A$1:$A$10<>"",ROW(Sheet1!$A$1:$A$10)-ROW(Sheet1!$A$1)+
1),ROWS($A$1:A1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Top