Pivot filter

Occasional Contributor

Hello, I am wondering if it is possible to create a pivot table, but have it filter based off a table, or list, that will be filled in on a set-up sheet. For instance, we have the pivots, and the raw data. But when I send this out, the individual will input the info related to them on the set-up sheet. I then want the pivot/pivots, to filter to only show the information related to what they input on the set-up page. 


I am not very good at explaining stuff. But say we sell apples in 4 states (TX, NC, SC, and KS). The pivot is pulling all of the information regarding all 4 states. However, when I send this to a rep in TX, I want it to where he can input 'TX' in the set-up page, and the pivots automatically update to reflect only the sales in TX. So like a filter of sorts based on what is input on a different sheet. While this example is fairly simple, and it seems it would be easier for this rep to simply filter the pivot to 'TX', this is just an example. Real world it is closer to 30-75 things being filtered out of the 800-1200 that are being pulled. So it would be much easier for them to be able to input their info into a table on a set-up page, rather than messing with every filter. 

1 Reply


PivotTable ignores source data filtering. The workaround could be to create intermediate dynamic source and build PivotTable on it. Technique is described here Pivot Table from Filtered List Visible Rows – Contextures Blog