Pivot Table Update

[Dale]

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.

 

[smartin]

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.

 

[Dale]

Thanks! Problem solved.

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.
.

 

[Roger Govier]

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 data>Data>List>Create>check my
List has Headers
Then Place your cursor in the first cell of this List and data>Pivot
Table>Finish 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 data>Insert tab>Table>check my table has
Headers>Give it a different name if required in the Table Name box of the
Design tab that appears
Place cursor in data table>Table Design>Summarise 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 PT>View 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

Thanks! Problem solved.



__________ 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

 

[Roger Govier]

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

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

 

[smartin]

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.