Pivot Table with report filter

**Jayakumar1313** · 07-29-2016, 01:18 PM

Dear Friends,

I have created a pivot table using vba macro, I have added few city names in report filter field, now the pivot table shows All options, but I want to select specific cities from specific ranges from sheet1. If i give 5 city names in Range(J1:J5), these names to be filter automatically in pivot table. Is it possible?

Regards,
Jay

**dflak** · 08-01-2016, 10:25 AM

It is possible but we will need a sample workbook to help you.

**Jayakumar1313** · 08-05-2016, 01:03 AM

Hi,

Sample report attached for you reference. I have selected multiple options in report filter. The same thing want to be filtered automatically by referring cell range.

Regards,
Jay

**dflak** · 08-09-2016, 09:24 AM

I just got back from vacation. I will look at this issue when I clear my backlog.

**dflak** · 08-09-2016, 01:14 PM

I made another pivot table in column L. You can move this pivot table anywhere. Even to a hidden sheet. I contains a unique list of cities found in the source data. I overlaid this with a named dynamic range: City_List =OFFSET(Sheet2!$L$2,0,0,COUNTA(Sheet2!$L:$L)-1,1). This range shrinks and grows with the list of cities. Its purpose is to provide data validation for cells J1:J5 so that only valid city names can be added there.

I defined the range J1:J5 as a static named range: Filter_List.

The basic logic of the program is to turn on all the cities in the pivot table. Then build a master string containing the selected cities. Then go back through the City Name filter for the pivot table and if a city name in the filter is not contained in the master string, turn it off.