Adaptable pivot tables

[Allenx]

What is a general technique for handling the follow situation? I use pivot
tables and pivot charts to analyze a dataset. Each week, the dataset's
record count increases (thus the rows the pivot table uses expands).

 

[Guest]

What if you just made your dataset range in the pivot table way greater than
the size of the actual data and then adjust the pivot table results to
exclude the blank rows.

For example, if your pivottable is for data on rows 1 - 1000, make the
pivottable range refer to rows 1-50000. Then just suppress the viewing of
the blank rows.

 

[Toyin.Butler]

Personally I like dynamic named ranges. Name a Range via the Name
Manager (2007) or Insert - Name (pre 2007)

In the Refers To box insert the following

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

This counts the number of values/text in the first column and the top
row and expands or contracts with the size of your range.

Reference your pivottable to this named range and all you will need to
do is refresh it.

Toy.