depending data in pivot table

I am looking for a way to show dependent data in a pivot table.

For instance, when I select a region, I would like to see only the related items to that region, not all possible items.



Region number

North 1

North 2

North 3

South 4

South 5

South 6



When creating a pivot table, I put these values in the page section.

Selecting a region and than the number, shows unfortunately all numbers and not just 1,2 and 3.

I’m thinking of creating a filter, copy the found data en than make a pivot table. The problem than is that when an other region is selected, I should update the filter and than update the pivot table, so that isn’t really a solution.


Is there a solution for the problem as described above?


Greetings



ncb

is this what you want?

Hi Ncb,

Your Query is Bit hard to understand !

Here Pivot is a Tool which operates data into required format but it depends the way use the fields like - Page , data & section

Probably you can drag the Page section to the field section so that it can point out the format u require.

Best regards
Surya

Hallo Mallycat,

That's almost what I want.
Thank you for answering.

I think I didn’t make myself quite clear.

Of course in the pivot table itself you can only see the related items.

But in the page field when clicking the small arrow, I would like to see there only the related items to for instance the region North.

So when in the page field I select a region, in the other page field I would like I could only choose the related items.

Referring to Access, it's a result of a query using in an other query.

Greetings,
ncb

Hallo Surya,

Thank you for answering.

I think I didn’t make myself quite clear.

Of course in the pivot table itself you can only see the related items.

But in the page field when clicking the small arrow, I would like to see there only the related items to for instance the region North.

So when in the page field I select a region, in the other page field I would like I could only choose the related items.

Referring to Access, it's a result of a query using in an other query.

Greetings,
ncb

OK, try this. Move the region from the page location into the main table. Then click on the drop down arrow against the region heading, and deselect one of the items and click OK. Then drag the region headind back to the page location. Any items you have selected/deselected will stay in that state.

Thanks.

It works for one region.
But when selecting an other region, the items belonging to that region don't come up.