Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column in a pivot table | Excel Discussion (Misc queries) | |||
Sorting a Pivot Table Column that is not the first column... | Excel Worksheet Functions | |||
Pivot table add one column | Charts and Charting in Excel | |||
Pivot table - Same column Name | Excel Discussion (Misc queries) | |||
Pivot Table Row/Column | Excel Discussion (Misc queries) |