Pivot Table refresh

G

Guest

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!
 
R

Roger Govier

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 PT>Table Options>untick
Auto Format table>tick Preserve Formatting.
 
G

Guest

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!
 
R

Roger Govier

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.
Data>List>Create List> tick my List has Headers

The List will automatically copy any formulae as new rows as added.
Whilst within the list
Data>List>>Totals
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.
 

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

Top