Adding Advanced Filter Critera w/o altering Pivot

[MrLookout9]

Hello and thank you for the help.

At work, I have a workbook with multiple tabs that contain lists. Each tab has a corresponding Pivot Table.

There is a business requirement that a user can specify a name, which filters the data. For the sake of simplicity use this for an example


Column Headers: First_Name ; Last_Name; Age
Row 1: Johnny; Bravo; 29
Row 2: Shane; Falco; 34
Row 3; Bobby; Shane; 15

The user specifies "Shane" as the filter in another pre-determined Cell (D1). Using advanced criteria, I need to find all rows that have Shane in either First_Name or Last_Name. The only way I know how to do that is inserting two rows and adding criteria:

First_Name ; Last_Name; Age
=D1; ;
;=D1;
Johnny; Bravo; 29
Shane; Falco; 34
Bobby; Shane; 15

With Criteria Range = "A1:B3"

This is problematic because my Pivot tables now include 5 rows of data. If there any way around this?

Thank you

 

[Jerry Sullivan]

Have you considered having your Advanced Filter Criteria Range be separate from your PivotTable data source range?

With Criteria Range = "F1:G3"

 

[MrLookout9]

That is a great idea. Thank you. I will let you know how that goes.

 

[MrLookout9]

Works. Thank you. You are a gentlemen and a scholar.