Function returning cells containing text and omitting blank cells.

[reszax]

Hey everyone,

I am very new to excel and have a question, which soem of you migth consider as quite basic and silly. I have tried to resolve it for over 3 hours now and miserably failed...

Situation:
Column which contains references to other worksheet within the excel workbook, and which gives two different types of output:
a) blank cell (however it contains a formula)
b) cell containg text (generated using a formula)

Objective:
Obtain a list of cells containing text only (i.e. omitting "blank" cells)

I have already tried using the sollution suggested here: http://www.excelforum.com/excel-form...m-a-range.html.
However, it has not worked. I think that it is because my "blank cells" are not really "blank" as they contain references to formulas in different spreadsheets of excel. When i deleted formulas in my "blank cell" it did work, but I need to keep them.

Any ideas?
example.xlsx

[Fotis1991]

There are not silly questions.. Only silly people that they believe that they know everything.

To your issue now.

I believe that in your case best way is to continue using that simple formula and use Custom Sorting option to get your results.

[reszax]

Thanks Fotis,

the issue is however that I need to keep it dynamic and automated (project)so that when other people insert some inputs and click F9 it gets automaticaly updated.

Do you any function that I could use to obtain the same results?

Thanks

[ARGK]

Hi
You can use a filter to do this. Highlight the range you want to filter and then click on the filter button and untick the blank option. Even though some cells have formulae they will be ignored if they have no value.
Hope this helps.
Good luck.
Tony

[Fotis1991]

Of course we can do this using formula but i tried to suggest the simplest way. So in B6 and copy down, use this ARRAY formula.

=IFERROR(INDEX(Input!$I$4:$I$20,SMALL(IF(Input!$G$4:$G$20=1,ROW(Input!$I$4:$I$20)-3),ROW(Input!I1))),"")


-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[reszax]

It all works as it is supposed to! Thank you!