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? :)
--
David Schreffler, EA

Dave Schreffler wrote:
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? :)

When you say "can't refresh" what exactly happens or doesn't happen?

If the problem is that the new data doesn't appear in the pivot table,
you could base the pivot table on a dynamic range, as described he

http://www.contextures.com/xlPivot01.html

Dave Schreffler wrote:
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? :)


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

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


"Dave Schreffler" wrote:

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? :)
--
David Schreffler, EA

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 InsertTable then base the PT on the Table created.
In XL 2003, the equivalent would be DataListCreate
Before XL2003, neither List nor Table existed.

--
Regards
Roger Govier

"willy" wrote in message
...
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


"Dave Schreffler" wrote:

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? :)
--
David Schreffler, EA

You can use dynamic named range for the source data :
exemple for data in columns A to D :
Insert / Name
Name : myRange
Refers to :
=OFFSET(Sheet1!$A$1:$D$1,,,COUNTA(Sheet1!$A:$A))
Have a look at the exemple file :
http://www.filedropper.com/excelpivottableproblem
HTH
Daniel

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? :)

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.

"Roger Govier" wrote:

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 InsertTable then base the PT on the Table created.
In XL 2003, the equivalent would be DataListCreate
Before XL2003, neither List nor Table existed.

--
Regards
Roger Govier

"willy" wrote in message
...
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


"Dave Schreffler" wrote:

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? :)
--
David Schreffler, EA

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 Featu PivotTable report from /xl/pivotTables/pivotTable2.xml part
(PivotTable view)
Removed Featu 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.

--
David Schreffler, EA



"smartin" wrote:

Dave Schreffler wrote:
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? :)

When you say "can't refresh" what exactly happens or doesn't happen?

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

Thanks ~ Dave S
--
David Schreffler, EA



"Debra Dalgleish" wrote:

If the problem is that the new data doesn't appear in the pivot table,
you could base the pivot table on a dynamic range, as described he

http://www.contextures.com/xlPivot01.html

Dave Schreffler wrote:
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? :)


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com