"Gavin" wrote:

On Aug 27, 10:31 am, Mr CheeZe
wrote:
I have created a pivot table where i need to change the data frequently and
sum up the total minutes per week of an activity.
i am using a 'week number' format and using'getpivotdata' to populate
another part of the spreadsheet.
the problem occurs when there is no data for a particular week and the pivot
table refresh deletes the week 'column' entirely therefore destroying my
'getpivotdata' format on the other page.

example

first set of pivot data;
week no; 31 32 33 34 35
mins 165 25 180 134 145

second set of pivot data;
week no; 31 32 34 35 37
mins 120 36 208 356 38
.................................................. .........
desired scenario for second set of data;
week no; 31 32 33 34 35 36 37
mins 120 36 0 208 356 0 38

how can i preserve the 'missing' weeks in my pivot table?
a zero is acceptable in the minutes row.

many thanks for your help

Mr CheeZe

In your second set of pivot data you need to show week no. 33 and the
mins of 0. If you are refreshing a pivot table, there has to be data
in order for it to populate.

My pivot table works excellently apart from the missing weeks when there is
no data.
This in turn messes up my references to these 'week' totals on another page
where i have a list of consecutive 'week ending' dates and want to populate
these with say a full 5 values when sometimes i only have 3 which do not run
consecutively.

Can you suggest a workaround or can I scrape the 'pivot'data from my raw
data in a different way?
My pivot works by totalling the mins spent at a particular location sorted
by 2 criteria, 'stop' in the data and a figure in brackets.