Page 1 of 1
Filter deleting #N/D
Posted: Wed Jan 16, 2019 3:23 pm
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
Re: Filter deleting #N/D
Posted: Wed Jan 16, 2019 5:29 pm
by RusselB
I'm going to suggest using Data -> Filter -> Standard Filter
Re: Filter deleting #N/D
Posted: Wed Jan 16, 2019 5:31 pm
by dawidek990
is not working, or i dont know how to set this ;]
Re: Filter deleting #N/D
Posted: Wed Jan 16, 2019 5:38 pm
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.
Re: Filter deleting #N/D
Posted: Fri Jan 18, 2019 1:03 pm
by dawidek990
ok, but is not wokring with #ARG!
Re: Filter deleting #N/D
Posted: Fri Jan 18, 2019 1:56 pm
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.)