Using Advanced Filter to remove blank cells from list: attempt producing formulae from original list- rather than resultant values- in filter result

[appreciatethehelp]

I’m trying to use the Advanced Filter to specify criteria that will remove blank cells from a list, leaving only cells not equal to nothing (criteria used: <>"" ) however I am not getting the intended result.

In the attached document The light blue column (column C) contains the list I want to remove the empty cells from, the medium blue column (D) contains the Advanced Filter criterion, and the dark blue column (F) contains the Filter Result.

Rather than applying the specified criterion to the values in the light blue column, it seems the result of the Filter is merely a reproduction of the formulae in the light blue column, thereby producing an undesired result.

How should I go about reproducing the light blue list in the same ascending order but with blank cells ommitted, without removing rows where blank cells reside? Will a reworked variant of this Advanced Filter approach work? If not what method is suitable?

Cheers.

Advanced Filter Example.ods

[JohnSUN]

First, select the “Incomplete Reading Sets” column and press the F4 key - this will change all relative references in the formulas to absolute, it will become =IF($B$2="YES";"";$A$2) instead of the previous =IF(B2="YES";"";A2). Now, when the filter transfers the formulas to a new column, they will still refer to your cells with data from columns A and B, and not to cells in columns E and D.

Change the filter condition, make two cells: in one specify “more than an empty string”, in the other “more than zero”. This will help the filter to select both strings like “01-02” and numeric ones.

Alternatively, you can use the Text to Columns tool to convert all the data in column A to text values. In this case, one condition “more than an empty string” is sufficient.

Now try filtering again