Workbook pivot table design function
in [Functions]
|
From: Jazz on 20 Feb 2010 18:46 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?
From: Max on 21 Feb 2010 02:38 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? >
From: Jazz on 21 Feb 2010 11:55 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? > >
From: Max on 21 Feb 2010 17:54 > $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 ---
From: Jazz on 21 Feb 2010 18:46
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 > --- |