Filter deleting #N/D

Discuss the spreadsheet application
Post Reply
dawidek990
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am

Filter deleting #N/D

Post by dawidek990 »

What to do if I have a formula on the list that sometimes shows me #N/D ... to show me the items that do not contain this #N/D?

for example:
asd
ert
fgh
#N/D
#N/D
#N/D
ashdg

and it should be:
asd
ert
fgh
ashdg
OpenOffice 4.1.6 Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Filter deleting #N/D

Post by RusselB »

I'm going to suggest using Data -> Filter -> Standard Filter
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
dawidek990
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am

Re: Filter deleting #N/D

Post by dawidek990 »

is not working, or i dont know how to set this ;]
OpenOffice 4.1.6 Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Filter deleting #N/D

Post by Lupp »

(Let me replace the "#N/D" ith "#N/A" which is the English / international variant.)

Input =NOT(ISNA(A2)) (or respectively adapted formula) into the second row (or adapted again) of a spare column and fill it down as far as needed. Then apply the Autofilter to the helper column and choose 1 instead of 'All'.

Please note: A filter applied in situ doesn't delete anything. It only (sort of) hides the rows not meeting the filter criteria. Internally Calc also knows if the rows made invisible are 'filtered' or directly hidden by the user.

There is an option to not filter in situ, but to copy the filtered results to a different range. In this case the data not matchingt the criteria are actually not contained in the copied result.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
dawidek990
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am

Re: Filter deleting #N/D

Post by dawidek990 »

ok, but is not wokring with #ARG!
OpenOffice 4.1.6 Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Filter deleting #N/D

Post by Lupp »

(I don't know the error "#ARG!" May it be the Polish version of either "#REF!" or "#VALUE!" ?)

The ISNA() function only detects the "#NA" error which may pass relevant information. If you want to detect any error, use ISERROR() instead. If you want to filter out errors of one specific types only, use the ERRORTYPE() function together with a comparator .

(To ease international cooperation in forums everybody can choose an English UserInterface under Tools > Options > Language Settings independent of the locale.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply