Filter in Pivot Table Field List Clearing when The Pivot Table Is Refreshed

[SteveP29]

I have a pivot table that takes data from a table, some of the data in the table takes data from another sheet within the workbook via lookup.

If there is no data in the other sheet, the table and then consequently, the pivot table displays the #N/A error as expected.

I have filtered the fields in the Field list (1st image)
The errors are still showing when I close the Field List.
When I refresh the pivot table, I am expecting the errors not to be removed from the pivot table, just leaving me the data I need.
This is not happening and when I check the Field List, the filters have been removed (2nd image).

I hope that explanation makes sense.

Can anyone suggest how I ensure the #N/A errors don't show on the pivot table?
Thanks.

 

[arun_eagertolearn]

Hi

If the entire entry involving the #N/A has to be removed, please add a helper column in the source data, say 'error' which would return "Yes" or "No" when an error is encountered
This could be kept as a page filter in the pivot table, and the errors can be removed

In case #N/A have to be replaced with blank, please go to Pivot Table Analyze--->Pivot Table (dropdown)--->Options--->Layout &Format(1st tab)--->Please tick 'for error values show' and type in the value that you may want to show (it could be blank also) in case of an error