I have a pivot table in Excel that imports data from Access. The data is for
a fiscal week (and is updated every week). We have a couple of columns
outside of the pivot table that give totals for data in the pivot table
(total hours, yield, etc..) Each week when a new week is added to the table
and we refresh, the pivot table is updated, but the totals outside the pivot
table don't change. Is there any way to link or connect these columns so
that they refresh and update along with the pivot table? Also, is there any
way to keep formatting the same when refreshing? For example, when I change
the column width and refresh, the width goes back to the way it was, even if
I click save first.
Thanks in advance!

Hi

How are the formulae set up for the calculations external to the PT?
If you are using GetPIvotData, then it may be that the references within
the formula are fixed to text values that existed in the PT when first
set up. If these values have now changed, you will get an error.
Change the references to relate to the cells of the PT containing the
text, rather than the text itself.

With regard to column width, right click on the PTTable Optionsuntick
Auto Format tabletick Preserve Formatting.

--
Regards

Roger Govier


"BK" wrote in message
...
I have a pivot table in Excel that imports data from Access. The data
is for
a fiscal week (and is updated every week). We have a couple of
columns
outside of the pivot table that give totals for data in the pivot
table
(total hours, yield, etc..) Each week when a new week is added to the
table
and we refresh, the pivot table is updated, but the totals outside the
pivot
table don't change. Is there any way to link or connect these columns
so
that they refresh and update along with the pivot table? Also, is
there any
way to keep formatting the same when refreshing? For example, when I
change
the column width and refresh, the width goes back to the way it was,
even if
I click save first.
Thanks in advance!

Hello,
Thanks for the help. As far as the formula, they just reference the cells
in a row of the pivot table. For example, the columns have the hours worked
by different employees for the week, and outside the PT is the total of all
the hours (and other calculations, such as yield%). But when a new week is
added (it shifts all data in the PT down one row to accomadate the new week),
the outside calculations and totals do nothing. I was just wondering if
there is a way to link or connect the outside rows with the PT so that the
total row will shift down also, and the new week's calculations will be right
above it?
Thanks!

"Roger Govier" wrote:

Hi

How are the formulae set up for the calculations external to the PT?
If you are using GetPIvotData, then it may be that the references within
the formula are fixed to text values that existed in the PT when first
set up. If these values have now changed, you will get an error.
Change the references to relate to the cells of the PT containing the
text, rather than the text itself.

With regard to column width, right click on the PTTable Optionsuntick
Auto Format tabletick Preserve Formatting.

--
Regards

Roger Govier


"BK" wrote in message
...
I have a pivot table in Excel that imports data from Access. The data
is for
a fiscal week (and is updated every week). We have a couple of
columns
outside of the pivot table that give totals for data in the pivot
table
(total hours, yield, etc..) Each week when a new week is added to the
table
and we refresh, the pivot table is updated, but the totals outside the
pivot
table don't change. Is there any way to link or connect these columns
so
that they refresh and update along with the pivot table? Also, is
there any
way to keep formatting the same when refreshing? For example, when I
change
the column width and refresh, the width goes back to the way it was,
even if
I click save first.
Thanks in advance!

Hi

If you have XL2003, then you could use the List function.

Assuming you have headers above your external calculations, mark an area
which includes the headers, but exceeds the number of rows currently
used.
DataListCreate List tick my List has Headers

The List will automatically copy any formulae as new rows as added.
Whilst within the list
DataListTotals
will add a Total row at the bottom of the list.

Now, as new rows are created in the PT, they will be included within
your data outside of the PT.

--
Regards

Roger Govier


"BK" wrote in message
...
Hello,
Thanks for the help. As far as the formula, they just reference the
cells
in a row of the pivot table. For example, the columns have the hours
worked
by different employees for the week, and outside the PT is the total
of all
the hours (and other calculations, such as yield%). But when a new
week is
added (it shifts all data in the PT down one row to accomadate the new
week),
the outside calculations and totals do nothing. I was just wondering
if
there is a way to link or connect the outside rows with the PT so that
the
total row will shift down also, and the new week's calculations will
be right
above it?
Thanks!

"Roger Govier" wrote:

Hi

How are the formulae set up for the calculations external to the PT?
If you are using GetPIvotData, then it may be that the references
within
the formula are fixed to text values that existed in the PT when
first
set up. If these values have now changed, you will get an error.
Change the references to relate to the cells of the PT containing the
text, rather than the text itself.

With regard to column width, right click on the PTTable
Optionsuntick
Auto Format tabletick Preserve Formatting.

--
Regards

Roger Govier


"BK" wrote in message
...
I have a pivot table in Excel that imports data from Access. The
data
is for
a fiscal week (and is updated every week). We have a couple of
columns
outside of the pivot table that give totals for data in the pivot
table
(total hours, yield, etc..) Each week when a new week is added to
the
table
and we refresh, the pivot table is updated, but the totals outside
the
pivot
table don't change. Is there any way to link or connect these
columns
so
that they refresh and update along with the pivot table? Also, is
there any
way to keep formatting the same when refreshing? For example, when
I
change
the column width and refresh, the width goes back to the way it
was,
even if
I click save first.
Thanks in advance!