Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
.... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

"MikeF" wrote:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

"Barb Reinhardt" wrote:

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

"MikeF" wrote:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

This is what you have:

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7:D$2000) to

Counta(Acc!$D:$D) and subtrack the number of rows you don't want to
include. You may need to play with this a bit.

Change this; COUNTA(acc!$4:$24))

to something like
COUNTA(Acc!$7:$7) and subtract the number of columns that may have data you
don't want to include.

It's a bit trial by error. Once you get the handle on Offset, you'll use it
all the time.

HTH,
Barb Reinhardt

"MikeF" wrote:


Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

"Barb Reinhardt" wrote:

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

"MikeF" wrote:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

Barb,

This ended up working ....

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$D$7:$X$7))

Thanx again!!
- Mike

"Barb Reinhardt" wrote:

This is what you have:

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7:D$2000) to

Counta(Acc!$D:$D) and subtrack the number of rows you don't want to
include. You may need to play with this a bit.

Change this; COUNTA(acc!$4:$24))

to something like
COUNTA(Acc!$7:$7) and subtract the number of columns that may have data you
don't want to include.

It's a bit trial by error. Once you get the handle on Offset, you'll use it
all the time.

HTH,
Barb Reinhardt

"MikeF" wrote:


Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

"Barb Reinhardt" wrote:

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

"MikeF" wrote:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

The only issue I'd have with your formula is what happens if the range ever
expands to the right or is longer than 2000? It sometimes happens and you
won't catch it with your pivot source.

"MikeF" wrote:

Barb,

This ended up working ....

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$D$7:$X$7))

Thanx again!!
- Mike

"Barb Reinhardt" wrote:

This is what you have:

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7:D$2000) to

Counta(Acc!$D:$D) and subtrack the number of rows you don't want to
include. You may need to play with this a bit.

Change this; COUNTA(acc!$4:$24))

to something like
COUNTA(Acc!$7:$7) and subtract the number of columns that may have data you
don't want to include.

It's a bit trial by error. Once you get the handle on Offset, you'll use it
all the time.

HTH,
Barb Reinhardt

"MikeF" wrote:


Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

"Barb Reinhardt" wrote:

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

"MikeF" wrote:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike