The data for my pivot table is an ever increasing list of rows with 20 fixed
columns. Rather than constantly extending the source data range, I have used
'Define Name' and created an OFFSET formula. However how do I now get the
pivot table to use this instaed of the original data range.

Thunderbirds,

Because yuor number of columns is fixed (20) and your number of rows is
increasing, a easy way to your pivot table ever "see" all the data is select
the range of the pivot table the 20 coluns and all the sheet rows:

for example: (Pivot Table Wizard Step 2 of 3:)

Range: $A$1:$T$65536

and at PT filters select to don´t show blank values...

so your pivot table always will "see" all the data inside this range!!!

i hope this solve your problem!!!

PEdro


"thunderbirds" wrote:

The data for my pivot table is an ever increasing list of rows with 20 fixed
columns. Rather than constantly extending the source data range, I have used
'Define Name' and created an OFFSET formula. However how do I now get the
pivot table to use this instaed of the original data range.

If you just want to change the source data range of your pivot table to a
name that you created....... Pull up the pivot table wizard. Click the back
button. You should now be at the part where you select the range. Delete
what is in the dialogue box currently. Then place your cursor in the field
and follow the Excel menu path Insert / Name / Paste. Choose the proper name
and click Ok.

Hope this helps.

Bill Horton

"thunderbirds" wrote:

The data for my pivot table is an ever increasing list of rows with 20 fixed
columns. Rather than constantly extending the source data range, I have used
'Define Name' and created an OFFSET formula. However how do I now get the
pivot table to use this instaed of the original data range.

Thanks, but how do I get in to PT filters so I can select 'don't show blanks'

"PedroPastre" wrote:

Thunderbirds,

Because yuor number of columns is fixed (20) and your number of rows is
increasing, a easy way to your pivot table ever "see" all the data is select
the range of the pivot table the 20 coluns and all the sheet rows:

for example: (Pivot Table Wizard Step 2 of 3:)

Range: $A$1:$T$65536

and at PT filters select to don´t show blank values...

so your pivot table always will "see" all the data inside this range!!!

i hope this solve your problem!!!

PEdro


"thunderbirds" wrote:

The data for my pivot table is an ever increasing list of rows with 20 fixed
columns. Rather than constantly extending the source data range, I have used
'Define Name' and created an OFFSET formula. However how do I now get the
pivot table to use this instaed of the original data range.

You would need to add a help column to your source and use a formula that
checks it, then add that as a field and uncheck the blanks for there, kinda
like

=IF(cell="","Blank","Not Blank")

then add it as a field and uncheck Blank
--

Regards,

Peo Sjoblom

"thunderbirds" wrote in message
...
Thanks, but how do I get in to PT filters so I can select 'don't show
blanks'

"PedroPastre" wrote:

Thunderbirds,

Because yuor number of columns is fixed (20) and your number of rows is
increasing, a easy way to your pivot table ever "see" all the data is
select
the range of the pivot table the 20 coluns and all the sheet rows:

for example: (Pivot Table Wizard Step 2 of 3:)

Range: $A$1:$T$65536

and at PT filters select to don´t show blank values...

so your pivot table always will "see" all the data inside this range!!!

i hope this solve your problem!!!

PEdro


"thunderbirds" wrote:

The data for my pivot table is an ever increasing list of rows with 20
fixed
columns. Rather than constantly extending the source data range, I
have used
'Define Name' and created an OFFSET formula. However how do I now get
the
pivot table to use this instaed of the original data range.