Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
I created a pivot table in a worksheet. The data for the pivot table comes
from another worksheet (same book). When I update the worksheet for the data the pivot table output doesn't reflect the update. I wouldn't think that I'd have to create a new pivot table each time...so I'm hoping that there's a way to have the output of the pivot table reflect the updated worksheet. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
Dale wrote:
I created a pivot table in a worksheet. The data for the pivot table comes from another worksheet (same book). When I update the worksheet for the data the pivot table output doesn't reflect the update. I wouldn't think that I'd have to create a new pivot table each time...so I'm hoping that there's a way to have the output of the pivot table reflect the updated worksheet. Thanks. The pivot table will not pick up new data until you refresh it. Right click the pivot table and select Refresh Data. Also, you may have limited the pivot table's source data to the original range of data -- hence any additional rows of data will not be picked up when you refresh the pivot table. To correct this, right click the pivot table, Pivot Table Wizard | Back | update the range appropriately. If you select full columns at this step you will never need to worry about the range problem again. Refreshing the pivot table will be necessary after changing the data range. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
Thanks! Problem solved.
"smartin" wrote: Dale wrote: I created a pivot table in a worksheet. The data for the pivot table comes from another worksheet (same book). When I update the worksheet for the data the pivot table output doesn't reflect the update. I wouldn't think that I'd have to create a new pivot table each time...so I'm hoping that there's a way to have the output of the pivot table reflect the updated worksheet. Thanks. The pivot table will not pick up new data until you refresh it. Right click the pivot table and select Refresh Data. Also, you may have limited the pivot table's source data to the original range of data -- hence any additional rows of data will not be picked up when you refresh the pivot table. To correct this, right click the pivot table, Pivot Table Wizard | Back | update the range appropriately. If you select full columns at this step you will never need to worry about the range problem again. Refreshing the pivot table will be necessary after changing the data range. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
Hi Dale
You would be very unwise to make your source range the whole columns. It will slow everything down, especially in XL2007 with 1 million rows. From a posting I made a couple of days ago It all depends upon your source data range being Dynamic. If your source is currently set as $A$1:$M$200 and you then add data to rows 201, 202 etc, then Refresh will have no effect, as you have told Excel only to use data down to row 200. Different options exist dependent upon the version of Excel you are using. For XL2003 Place your cursor in the first row of your dataDataListCreatecheck my List has Headers Then Place your cursor in the first cell of this List and dataPivot TableFinish and it will use the List as the source. The List is Dynamic and will grow as you add more data. In XL2007 Place cursor in first row of dataInsert tabTablecheck my table has HeadersGive it a different name if required in the Table Name box of the Design tab that appears Place cursor in data tableTable DesignSummarise with Pivot Table. For any version of Excel, you can create your own Dynamic named ranges. For more information on this take a look at a tutorial I wrote at http://www.contextures.com/xlDataVal15.html If you want your Pivot Table to update automatically, without having to click the Refresh button, then, assuming your PT is on a different sheet to the source data, copy this simple code to the PT Sheet. Private Sub Worksheet_Activate() ActiveSheet.PivotTables(1).PivotCache.Refresh End Sub To USE Copy code as above Right click on sheet tab with PTView Code Paste Code into white pane that appears Alt+F11 to return to Excel I hope that this helps clarify the issue -- Regards Roger Govier "Dale" wrote in message ... Thanks! Problem solved. "smartin" wrote: Dale wrote: I created a pivot table in a worksheet. The data for the pivot table comes from another worksheet (same book). When I update the worksheet for the data the pivot table output doesn't reflect the update. I wouldn't think that I'd have to create a new pivot table each time...so I'm hoping that there's a way to have the output of the pivot table reflect the updated worksheet. Thanks. The pivot table will not pick up new data until you refresh it. Right click the pivot table and select Refresh Data. Also, you may have limited the pivot table's source data to the original range of data -- hence any additional rows of data will not be picked up when you refresh the pivot table. To correct this, right click the pivot table, Pivot Table Wizard | Back | update the range appropriately. If you select full columns at this step you will never need to worry about the range problem again. Refreshing the pivot table will be necessary after changing the data range. . __________ Information from ESET Smart Security, version of virus signature database 4539 (20091024) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4539 (20091024) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
Roger Govier wrote:
Hi Dale You would be very unwise to make your source range the whole columns. It will slow everything down, especially in XL2007 with 1 million rows. From a posting I made a couple of days ago It all depends upon your source data range being Dynamic. If your source is currently set as $A$1:$M$200 and you then add data to rows 201, 202 etc, then Refresh will have no effect, as you have told Excel only to use data down to row 200. Different options exist dependent upon the version of Excel you are using. For XL2003 Place your cursor in the first row of your dataDataListCreatecheck my List has Headers Then Place your cursor in the first cell of this List and dataPivot TableFinish and it will use the List as the source. The List is Dynamic and will grow as you add more data. In XL2007 Place cursor in first row of dataInsert tabTablecheck my table has HeadersGive it a different name if required in the Table Name box of the Design tab that appears Place cursor in data tableTable DesignSummarise with Pivot Table. For any version of Excel, you can create your own Dynamic named ranges. For more information on this take a look at a tutorial I wrote at http://www.contextures.com/xlDataVal15.html If you want your Pivot Table to update automatically, without having to click the Refresh button, then, assuming your PT is on a different sheet to the source data, copy this simple code to the PT Sheet. Private Sub Worksheet_Activate() ActiveSheet.PivotTables(1).PivotCache.Refresh End Sub To USE Copy code as above Right click on sheet tab with PTView Code Paste Code into white pane that appears Alt+F11 to return to Excel I hope that this helps clarify the issue All good points, but I would like to share my experience. Using E2003, I see no performance hit whatsoever when referencing full columns. I have hundreds of pivot tables that do this, and often have 5 or more caches in a workbook. I suppose when E2007 rolls around to my company I will get to see how well (or poorly) this technique scales. The only downsides of full columns in E2003, both minuscule, are (blank) becomes a PT category and numeric data items do not default to Sum. The advantage is I can easily refresh a cache with a single click, and not resort to updating the (potentially large) cache every time I activate the PT, as the VBA solution would do. Perhaps a better alternative would be to call that code on demand with a toolbar button or some such. Lists do not seem to work well in E2003. I have found that a worksheet with a list will cause Excel to use 100% CPU while the worksheet is active. This may not happen the first time the List is created, nor the second, but eventually it seems to happen. Because of this, I gave up using Lists as dynamic ranges. I suspect this is a bug and possibly a rare one (I have not researched it); I hope it has been corrected in E2007. A formula-driven dynamic range seems like a reasonable choice if you do not mind setting it up and embedding volatile formulas in the workbook. Regards, Smartin. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
Hi
Sorry to hear of your problems with List. I haven't experienced those. I guess because I started well before Lists were on the scene, I tend to use my own Dynamic Ranges most of the time. They do not have to be VOLATILE. I hate volatility, and if you look through the tutorial I pointed you to, you will see I use purely Index and Match, for all of my ranges, both of which are fast and non-volatile. There is a macro in the tutorial to create dynamic named ranges for you. Having blank rows at the end of my data has never ever been acceptable to me. I invariably have dates in some of my fields, and empty cells prevents the use of Grouping by Month, Quarter Year etc. Each to their own, but hundreds or thousands of superfluous rows is not the way for me. -- Regards Roger Govier "smartin" wrote in message ... Roger Govier wrote: Hi Dale You would be very unwise to make your source range the whole columns. It will slow everything down, especially in XL2007 with 1 million rows. From a posting I made a couple of days ago It all depends upon your source data range being Dynamic. If your source is currently set as $A$1:$M$200 and you then add data to rows 201, 202 etc, then Refresh will have no effect, as you have told Excel only to use data down to row 200. Different options exist dependent upon the version of Excel you are using. For XL2003 Place your cursor in the first row of your dataDataListCreatecheck my List has Headers Then Place your cursor in the first cell of this List and dataPivot TableFinish and it will use the List as the source. The List is Dynamic and will grow as you add more data. In XL2007 Place cursor in first row of dataInsert tabTablecheck my table has HeadersGive it a different name if required in the Table Name box of the Design tab that appears Place cursor in data tableTable DesignSummarise with Pivot Table. For any version of Excel, you can create your own Dynamic named ranges. For more information on this take a look at a tutorial I wrote at http://www.contextures.com/xlDataVal15.html If you want your Pivot Table to update automatically, without having to click the Refresh button, then, assuming your PT is on a different sheet to the source data, copy this simple code to the PT Sheet. Private Sub Worksheet_Activate() ActiveSheet.PivotTables(1).PivotCache.Refresh End Sub To USE Copy code as above Right click on sheet tab with PTView Code Paste Code into white pane that appears Alt+F11 to return to Excel I hope that this helps clarify the issue All good points, but I would like to share my experience. Using E2003, I see no performance hit whatsoever when referencing full columns. I have hundreds of pivot tables that do this, and often have 5 or more caches in a workbook. I suppose when E2007 rolls around to my company I will get to see how well (or poorly) this technique scales. The only downsides of full columns in E2003, both minuscule, are (blank) becomes a PT category and numeric data items do not default to Sum. The advantage is I can easily refresh a cache with a single click, and not resort to updating the (potentially large) cache every time I activate the PT, as the VBA solution would do. Perhaps a better alternative would be to call that code on demand with a toolbar button or some such. Lists do not seem to work well in E2003. I have found that a worksheet with a list will cause Excel to use 100% CPU while the worksheet is active. This may not happen the first time the List is created, nor the second, but eventually it seems to happen. Because of this, I gave up using Lists as dynamic ranges. I suspect this is a bug and possibly a rare one (I have not researched it); I hope it has been corrected in E2007. A formula-driven dynamic range seems like a reasonable choice if you do not mind setting it up and embedding volatile formulas in the workbook. Regards, Smartin. __________ Information from ESET Smart Security, version of virus signature database 4541 (20091025) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4541 (20091025) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Update
Roger Govier wrote:
Hi Sorry to hear of your problems with List. I haven't experienced those. I guess because I started well before Lists were on the scene, I tend to use my own Dynamic Ranges most of the time. They do not have to be VOLATILE. I hate volatility, and if you look through the tutorial I pointed you to, you will see I use purely Index and Match, for all of my ranges, both of which are fast and non-volatile. There is a macro in the tutorial to create dynamic named ranges for you. Having blank rows at the end of my data has never ever been acceptable to me. I invariably have dates in some of my fields, and empty cells prevents the use of Grouping by Month, Quarter Year etc. Each to their own, but hundreds or thousands of superfluous rows is not the way for me. I'm not averse to trying new things. I shall give your dynamic range a try. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table update | Excel Discussion (Misc queries) | |||
auto update my pivot table | Excel Discussion (Misc queries) | |||
Auto update of Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Update | Excel Discussion (Misc queries) | |||
Pivot Table update | Excel Discussion (Misc queries) |