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),COUNTA(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),COUNTA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7$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),COUNTA(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),COUNTA(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),COUNTA(acc!$4:$24))
>
> I'd tweek a couple of sections.
>
> Change CountA(acc!$D7$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),COUNTA(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),COUNTA(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),COUNTA(acc!$4:$24))
> >
> > I'd tweek a couple of sections.
> >
> > Change CountA(acc!$D7$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),COUNTA(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
> > > > >
> > > > >