Excel Pivot Table problem

D

Dave Schreffler

I create pivot tables in a time tracking worksheet I use each month. However,
when I go back to a saved worksheet and enter new data, I can't refresh the
pivot tables. Therefore, I have to re-build the pivot tables in each
employee's worksheet at the end of each month.

What am I doing wrong? :)
 
W

willy

When you select the data, choose the whole column, by clicking on B and
extending the selection C D etc. This will set the PVT to look all the way to
the bottom all the time. With modern computers this is a mere trifle and
costs nothing in time, even with XL2007 going down 1048576 rows.
You can check this on old PVTs by clicking once on a cell in the PVT, and
then (in XL07) going to the menu PIVOT TOOL OPTIONS CHANGE DATA SOURCE and
seeing what the range is. To get rid of the row restriction use
Sheetname!$B:$F

Ciao Willy
 
R

Roger Govier

Hi Willy

One problem with the approach you suggest, is the data will contain lots of
blanks.
If the OP wants to group dates, for example, into Weeks, Months or Quarters,
then they would not be able to.
Grouping dates will fail if any entry is blank or text.

Far better to go with Debra's dynamic range method.
Or if in XL2007 choose Insert>Table then base the PT on the Table created.
In XL 2003, the equivalent would be Data>List>Create
Before XL2003, neither List nor Table existed.
 
W

willy

Well

I certainly learnt something here. I had a play around with tables and PVTs
for 1/2 an hour and now I see a bit of logic. Many thanks Roger.

I had shied away from tables because it seemed to do a lot of stuff
automatically that I thought was of no benefit, but now I see there is some
method to the madness in the features of XL........

Thanks again.
 
D

Dave Schreffler

I need to be more clear. I have a static range on a tab that extracts data
from other tabs by formula. I enter data on another tab and save the
workbook. When I attempt to re-open, I get message "Excel found unreadable
content in [workbook name]. Do you want to recover the contents of this
workbook? If you trust the source of this workbook, click Yes." When I do so,
I get a "Repairs to [workbook name]" box that Excel was able to open the file
by repairing or removing the unreadable content, and tells me it removed
PivotTable reports and records. If I click to view file log listing repairs,
I see:
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part
(PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part
(PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

Hope this helps! By the way, I get the same results whether or not the tab
containing the pivot tables is protected.
 
D

Dave Schreffler

I clarified (I think) the problem in a 1/19 post replying to smartin. Do you
have any ideas on that?

Thanks ~ Dave S
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top