Update Filter List AFTER Filter Applied

[Robert Jacobs]

Thank you in advance, experts!!!!!

I have an Excel file with a couple of pivot tables. Everything works
perfectly, but I would like one more piece of functionality, if it's
possible.

My raw data includes 6 columns, which are Cust#, CustName, SalesRep,
InvAmount, InvDate, and CustCategory. When I go to my pivot table, I
have it setup like:

Report Filters: Cust#, CustName, SalesRep, CustCategory
Row Labels: InvDate
Values: Sum of InvAmount

When I apply a filter, such as I display all information regarding the
SalesRep, the filter works perfectly, showing me only InvDates and
InvAmount sums for that SalesRep. BUT, when I want to apply a second
filter, the filter list itself still contains all Cust#, CustName, and
CustCategory options for all sales reps. Is it possible to filter the
list once a filter has been applied, so if I filter down to a single
SalesRep, I only see his/her accounts listed in the other filter
lists?

Man, that was hard to word, and I still don't know if I got it
right... PLEASE HELP if possible!!! Thanks guys!!!!!!!!

 

[isabelle]

hi,

you can use this event, it occurs during the update of PivotTable,
Private Sub Worksheet_PivotTableUpdate()
please tell us if you need more informations to do applied

 

[Robert Jacobs]

hi,

you can use this event, it occurs during the update of PivotTable,
Private Sub Worksheet_PivotTableUpdate()
please tell us if you need more informations to do applied

I can't seem to figure out what to do when the PivottableUpdate()
event happens. I have tried refreshing the table, but that goes on an
infinite loop. I also found the event for
PivotTableSelectionChange(), which seems to be more what I'm looking
for, but refreshing the table doesn't refresh what's selectable from
the drop down list.

Any help is greatly appreciated! Thanks!

 

[Robert Jacobs]

Well.... after a bit of searching, it's looking like the Report
filters are not dependent. No matter what you select in one report
filter, every record is still listed in the other filters, even if
they won't show any data when selected. Apparently there's no real
way to do this, other than upgrading to Excel 2010 (for every person
using this document) and deploying Slicer... Thanks for looking, but
I guess this is impossible (wish it wasn't as it would be
EXTRAORDINARALLY helpful)