MS Excel 2003 - Pivot Table

[informer30]

Hi All,

I am looking at a Excel 2003 workbook which has a pivot table, the pivot table
references a sheet which I append with data. How can I increase the range of the
source sheet so the pivot table picks up all the appended data....

Many Thanks

 

[SkipVought]

Hi,

A solution is in faq68-1331, item 4.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Best bet is to create a "dynamic range"

Select the top left cell of your data.

Follow Insert>Name>Define

Enter a name for your range e.g. dr_Pivot

In the range definition box enter (assuming top left cell is A1 on a sheet called Sheet1

=OFFSET(Sheet1!$A$1,,,counta(Sheet1!$A:$A,counta(Sheet1!$1:$1)

Add the range

In your pivottable, go to the source data and enter

=dr_Pivot (or whatever you have called your range)

The range will redefine itself as the data changes size and all you will need to do is refresh the pivot table after you add mroe data



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question