Remove row if cell contains #N/A ...and remove duplicates

[PivotMeThis]

I have a report that contains a lookup table, a pivot table and code/formatting for a drill down report.

Data contains 4 lookups for various items about completed projects, but not all projects are completed so there are a lot of #N/A in the data. This sheet is hidden.

The pivot table summarizes the data for completed projects by FY/Quarter and dollar amount.

There is code to format the drill down report so that when a user double clicks on the pivot table only certain data will be made available in the report.

The problem is all the #N/As that appear. I am looking for some code that would remove the row if #N/A appears in column "Q" on the drill down reports.

The other problem is the duplicates. This report lists all work orders that were written for each project. So the project may be listed multiple times. The pivot table and drill down report show project totals only so anything duplicated in column "M" should be removed so that it only appears once on the drill down reports.

Seems like a tall order. Hope someone can help me.
Thanks!

 

[PivotMeThis]

Hi Jerry!

I think I need to start including my file names in my posts so I can track them down easier. I must have discontinued this particular report because I can't find this code but I sure appreciate you keeping these things up to date. This is something I can keep in my back pocket for future use.

It's always a pleasure to see your smiling face!
Take care
Rhonda

 

[Jerry Sullivan]

Nice to hear from you again Rhonda. Cheers!