Try to "filter" a Pivot table field

[feipezi]

Hi, folks,
I've been using Pivot tables for a while but one thing that bothers me a lot but I cannot do anything about it.
Suppose I created a Pivot table that had at least 2 page fields. One was called 'State' and the other 'County'. We all know one state has more than one county, i.e., the state is bigger.
If I selected state: NJ, the County field was supposed to show all the counties in NJ and that's what I expected. In fact, it would show all the counties in the 50 states.
If I put Autofilter on the same data source, it will give me what I expected. But P-tables cannot.
I know Pivot tables themselves do not have any functionality to fix this kind of situation so I am wondering if VBA can solve the problem.
Hope I made myself clear.
Thanks in advance.
feipezi.

 

[PHV]

Can't you base your pivot table on a query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[feipezi]

Thanks PH but could you be more specific: like you want ADO involved in the process?
feipe

 

[PHV]

NO, follow the PivotTable wizard : external data -> excel files -> your spreadsheet ...

Tip: when you're OK with the result, redo the whole thing with the macro recorder on ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Short of following PHV's excellent suggestion, which is what I would do, create a column to the left or right of the QT that references the item in question and AutoFilter on THAT column.

Skip,

_[red][/red]

 

[SkipVought]

...and since you're in the VBA forum...

each Pivot field in the ROW area HAS an internal filter. You can programatically set that filter based on a Drop Down list of those PivotItem values, but its kinda tricky.

Skip,

_[red][/red]

 

[KenWright]

A little kludgy perhaps, but if you knew for sure your page field would be the only page field on your sheet, and that it would be in the same place, then you could always just add another field to your source data, and have it populate with something like

=IF(data_in_county_field = page field, data_in_county_field,"")

=IF(page_field="(All)",data_in_county_field,IF(page_field=data_in_county_field,data_in_county_field,""))

That way you then use that new field as your county page field, and as long as you refresh the data each time you change the page field (Tie it to an event macro is easiest) you will only see the relevant records for that state, plus one blank selection option.

Only caveat is the worry if you change your page field location, so hence the 'kludgy' bit, but otherwise it should do what you need.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Ignore the first bit about it being the only page field on the sheet - absolutely no idea what I was thinking when I wrote that

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[feipezi]

Hi, folks,
Thanks for the hints and ideas.
I didn't have time trying until a couple of days ago.
First I followed PH's advice but the new problem came up. As I did it at home, it was OK but as I tried it at the workstation, I had a message like the following:

ODBC Excel Driver Login Failed
Unrecognized Database "c:\pathofthefile...xls"

This was what happened when I recorded a macro. If I just created a P-tabel without recording, then everything was fine.

I went to VBA View Code window and Tools->Reference and checked all the libraries that might have something to do with the situation. But not working yet.
I guess it may be because the ODBC driver was not properly set up.

Please advise.
Thanks.
Fei.