Consequences of changing the base table for PivotTable Reports

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

After creating several PivotTable reports, I found that I
needed to add a new column to the original base table. As
a result, I lost the column to the right of the newly
inserted column. Is there anyway that I can redefine the
range of the base table, to include the added and shifted
columns, without rebuilding each entire PivotTable report
from scratch?
 
Can you pick a column that always has data?

And can you use row, too.

I used column A for my columns and Row 1 for my headers (no gaps in either).

Then Insert|name|define
Give it a nice name (myPTRange)

and give it a formula like this:

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

When you're ready to test to see what it refers to, just hit F5 and type in that
name you used.

If it selects the correct region, tada!

If not, take a look here to see if this helps:

http://www.contextures.com/xlNames01.html#Dynamic

It's Debra Dalgleish's version.
 
Back
Top