Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Pivot table column help

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Pivot table column help

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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Pivot table column help



"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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
column in a pivot table Julie Excel Discussion (Misc queries) 1 August 26th 09 05:40 PM
Sorting a Pivot Table Column that is not the first column... [email protected] Excel Worksheet Functions 1 October 10th 07 09:02 PM
Pivot table add one column Daniel Charts and Charting in Excel 0 July 25th 07 12:00 AM
Pivot table - Same column Name Portuga Excel Discussion (Misc queries) 4 August 2nd 06 02:53 PM
Pivot Table Row/Column jim314 Excel Discussion (Misc queries) 5 December 29th 04 03:33 AM


All times are GMT +1. The time now is 05:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"