excel 2003 - Maintaining Pivot table structure when filtering

[Hillsy7]

For years I've bypassed this issue by using SUMIF constructed tables, or VBA macros. Finally come across a few problems I can't adequately fix with it. It's probably simple.

On a pivot table in excel 2003, is there a way to lock the row and column data so that, when a filter is applied it uses all possible pivot outcomes, rather than only those relevant to the filtered Data.
So for instance, say I have the following table

<CODE>Site .....Delivery Day ..Delivery Month .....Delivery Amount
Aberdeen .1 .............Jan ................14
York .....6 .............Feb ................28
Leeds ....30 ............Jan ................11</CODE>

</PRE>
.........and so on. So it's a huge table, big enough that there are entries for every day of the month (1-31) and every Month (Jan-Dec) , though not at least 1 delivery on every day of the year.

So when I pivot, I'll get a lovely table with the amount of deliveries for the year:

<CODE>___ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
1 ..14 .0 ..0 ..0 ..5 ..0 ..0 ..96 .0 ..26 .12 .0 ..143
2 ..0 ..10 .0 ........................</CODE>

</PRE>And so on....obviously formatted better.

ok so now I want to stick a page filter for Sites. No probs. However as soon as I pick a Site, it will only use months and days relevant to that site, NOT those on the entire table and only filtering the results within the table.

Is there any way round this without having to code the entire table into VBA or using SUMIF/SUMPRODUCT functions. Even if it's just a VBA prompt I can stick into the onchange macro for the work sheet. I have worked around it, but I'm using 20000 rows of data and the macro I've written takes time to work. Fine for me but I'm intending this for use by other, less technically adept, people. Hence why I need everything to stay in one place so I can display the information the same each time.

I'm open to other work arounds too that won't take 20-30 seconds to solve.

Thanks

 

[Hillsy7]

bump

 

[RoryA]

You can specify the 'Show Items with no data' option in the Field Settings for the Day and Month fields. As long as there is at least one entry for each day and for each month, you will see them all shown for each site.

 

[Hillsy7]

THANK YOU!!!

Doh I knew it'd be something simple.....was trying every possible combination of check boxes on the **** table options and summed data fields!!!!

 

[RoryA]

Glad to help.

A belated welcome to the forum, by the way.