Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
I used Sheet1 as the data source to create a pivot table in a new worksheet
which I named Sheet PT. Is it possible to design the workbook so that I can delete the data in Sheet1 and then paste new data with more or less rows in Sheet1 and have the pivot table in Sheet PT update to reflect the new data that has been pasted into Sheet1 but the formating of the pivot table stays the same? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
One thought is to simply set the pivot's source range (In step 2 of the PT
wizard) to cover the max expected extent of source data at the onset, then configure the base PT format. Then just refresh the PT to recalc for any new data added. The PT format should generally remain intact. Any help? hit YES below. -- Max Singapore --- "Jazz" wrote: I used Sheet1 as the data source to create a pivot table in a new worksheet which I named Sheet PT. Is it possible to design the workbook so that I can delete the data in Sheet1 and then paste new data with more or less rows in Sheet1 and have the pivot table in Sheet PT update to reflect the new data that has been pasted into Sheet1 but the formating of the pivot table stays the same? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
Hi Max, thank you for the feedback.
I understand what you meant when you said set the pivot's source range to cover the max extent of source data. For instance if my range is $A$1:$AI$500 that would mean change it to $A$1:$AI$60000 in Step 2 of Pivot table wizard. I need help figuring out how to -Configure the base PT format -Refresh the PT to recalc for any new data added Could you explain those steps to me as well? "Max" wrote: One thought is to simply set the pivot's source range (In step 2 of the PT wizard) to cover the max expected extent of source data at the onset, then configure the base PT format. Then just refresh the PT to recalc for any new data added. The PT format should generally remain intact. Any help? hit YES below. -- Max Singapore --- "Jazz" wrote: I used Sheet1 as the data source to create a pivot table in a new worksheet which I named Sheet PT. Is it possible to design the workbook so that I can delete the data in Sheet1 and then paste new data with more or less rows in Sheet1 and have the pivot table in Sheet PT update to reflect the new data that has been pasted into Sheet1 but the formating of the pivot table stays the same? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
$A$1:$AI$500 that would mean change it to $A$1:$AI$60000
But that's simply too great an increase? Do a self estimate based on say, the last 3 monthly variations in the extent (if its monthly data), then go for the smallest increase just large enough -Configure the base PT format By base, its meant that you create your own pivot w/o applying any hidden items -Refresh the PT to recalc for any new data added Right-click anywhere inside the PT, then click the exclamation button to refresh. The pivot needs to be refreshed each time there's a change in underlying source data -- Max Singapore --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
Phenomenal! Thanks for clarifying Max.
I have two questions remaining. I am sure I will feel silly when I realize what you meant but can you explain this statement. "By base, its meant that you create your own pivot w/o applying any hidden items" I think I know but I want to make sure I got it right. These are the steps I've been taking. Did I interpret all your instructions correctly? - Created the pivot table - Return to its source data and delete it - Entered new source data - Return to pivot table, right click, and choose Refresh Data - Click one of the downward arrows on the pivot table and de-select (blank) so the blank rows of data are hidden. Thanks for your time, Jazz "Max" wrote: $A$1:$AI$500 that would mean change it to $A$1:$AI$60000 But that's simply too great an increase? Do a self estimate based on say, the last 3 monthly variations in the extent (if its monthly data), then go for the smallest increase just large enough -Configure the base PT format By base, its meant that you create your own pivot w/o applying any hidden items -Refresh the PT to recalc for any new data added Right-click anywhere inside the PT, then click the exclamation button to refresh. The pivot needs to be refreshed each time there's a change in underlying source data -- Max Singapore --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
Hi,
Just convert the pivot table source data to a List/Table (in Excel 2007) by pressing Ctrl+L. Select the data including the header row and press Ctrl+L. When one converts a range to a List/Table, it becomes auto expanding. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Jazz" wrote in message ... I used Sheet1 as the data source to create a pivot table in a new worksheet which I named Sheet PT. Is it possible to design the workbook so that I can delete the data in Sheet1 and then paste new data with more or less rows in Sheet1 and have the pivot table in Sheet PT update to reflect the new data that has been pasted into Sheet1 but the formating of the pivot table stays the same? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workbook pivot table design function
The refrain given was based on my own experience working with PTs. Sometimes,
refreshing PTs with "filters" applied would unravel some aspects of it, and you need to then re-apply it again post refresh. And sometimes its ok. Best then that you experiment and build it up from your own experience. -- Max Singapore --- "Jazz" wrote: Phenomenal! Thanks for clarifying Max. I have two questions remaining. I am sure I will feel silly when I realize what you meant but can you explain this statement. "By base, its meant that you create your own pivot w/o applying any hidden items" I think I know but I want to make sure I got it right. These are the steps I've been taking. Did I interpret all your instructions correctly? - Created the pivot table - Return to its source data and delete it - Entered new source data - Return to pivot table, right click, and choose Refresh Data - Click one of the downward arrows on the pivot table and de-select (blank) so the blank rows of data are hidden. Thanks for your time, Jazz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh pivot table in workbook A when changing a cell in workbook | Excel Worksheet Functions | |||
Pivot Table design problems (% of grand total etc) | Excel Discussion (Misc queries) | |||
Pivot Table Design | Excel Worksheet Functions | |||
Workbook Design | Excel Discussion (Misc queries) | |||
How to copy pivot table workbook to new workbook? | New Users to Excel |