I have a data sheet that gathers sales data, which is then reported on by a
number of related Pivot tables. As the sales data (by row) increases each
time new orders are taken, the pivot tables data range reference becomes
invalid; resulting in a fair chunk of manual re-working of the data ranges to
get the right reporting.

e.g.
Last data range was Sales!$A$1:$AM$7370
I capture new sales so rows increase range to Sales!$A$1:$AM$7400
Pivot reports still only refer to Sales!$A$1:$AM$7370 and miss 30 new rows
in reporting.

How can I allow pivot tables to always refer to maximum populated values in
data range, without having to rework each Pivot report?

Check out this article at Debra Dalgleish's excellent Contextures site. It
explains how to create a pivot table with a dynamic data range:
http://www.contextures.com/xlPivot01.html

Hope this helps,

Hutch

"Noakesi" wrote:

I have a data sheet that gathers sales data, which is then reported on by a
number of related Pivot tables. As the sales data (by row) increases each
time new orders are taken, the pivot tables data range reference becomes
invalid; resulting in a fair chunk of manual re-working of the data ranges to
get the right reporting.

e.g.
Last data range was Sales!$A$1:$AM$7370
I capture new sales so rows increase range to Sales!$A$1:$AM$7400
Pivot reports still only refer to Sales!$A$1:$AM$7370 and miss 30 new rows
in reporting.

How can I allow pivot tables to always refer to maximum populated values in
data range, without having to rework each Pivot report?