Linking Multiple Page Fields in Pivot Table

**dfs3000my** · 03-01-2010, 12:33 AM

Hi,

I am trying to ensure that Page Fields in my Pivot Table is linked. Let's say my data as follows:

Please Login or Register  to view this content.

When I put Region and Country as my Page Field in the Pivot Table, and select the Region, Asia, I only want to see Japan, Korea, Singapore in my Country Page Field. What it is showing now is all the countries including Germany and Italy even though the region Asia was selected.

How can this be done and if it requires VBA programming, kindly advice. I am really not familiar with the Excel VBA syntax.

**teylyn** · 03-01-2010, 01:15 AM

Hi,

put just Region in the page field, then filter the country in the row field.

**dfs3000my** · 03-01-2010, 01:39 AM

Yes that is one alternative but however, my requirement requires both Region and Country to be put as Page Fields. Is there a workaround for this without putting Country to the Row Field?

**dfs3000my** · 03-01-2010, 02:00 AM

If this requires VBA programming, should I post this in the VBA programming section instead?

**teylyn** · 03-01-2010, 02:48 AM

I will move it for you.

**DonkeyOte** · 03-01-2010, 05:06 AM

As you've discovered - dependent Page Fields is not standard functionality and you will need to develop a workaround.

One possibility discussed previously: http://www.excelforum.com/excel-2007...rill-down.html

**dfs3000my** · 03-01-2010, 09:19 PM

Originally Posted by DonkeyOte

As you've discovered - dependent Page Fields is not standard functionality and you will need to develop a workaround.

One possibility discussed previously: http://www.excelforum.com/excel-2007...rill-down.html

Hi DonkeyOte,

Thanks for your reply. I'm trying to make this work on Excel 2003 though and I can't seem to find the "Retain items deleted from the data source" as mentioned in http://www.excelforum.com/excel-2007...rill-down.html post.

So far, I'm encountering a problem whenever I choose a different Make as the Models Page Field is not refreshed. Can you highlight me on what I should be doing please?

I have attached the file for your reference.

Thanks a bunch!!

**DonkeyOte** · 03-02-2010, 03:56 AM

Regards list not refreshing - you need to ensure you're running the file in Auto Calculation mode.

Regards old items - adapt the code to:

Please Login or Register  to view this content.

(the Retain items mentioned in the other post is available 2007 & beyond - prior to that it must be adjusted via code)

**dfs3000my** · 03-02-2010, 05:02 AM

Thanks DonkeyOte! Looks like it has solved my woes now. I've set my calculation mode to Automatic and for those who are not sure, you can go to Tools -> Options -> Calculation tab and change the mode to Automatic.

I have also attached the finalized file for those of you who may want to refer to.