Pivot Tables Overtaking Non-Pivot Rows

[jras414]

Hi All,

I am trying to format a pivot table so that it doesn't overtake and delete
any information contained in cells that are below it. When I refresh my
table's data, it sometimes becomes longer than it was originally formatted to
be, and it deletes the information in the cells below it; it sort of looks
like this, before and after:

Before Refresh:
_______________________________________
| Group 1 Group 2 Group 3|
|Item 1 blah blah blah | <---Pivot Table
|Item 2 blah blah blah |
|______________________________________|
CellInfo1 CellInfo2 CellInfo3 CellInfo4 <---Cells with
Non-Pivot Values

After Refresh:
_______________________________________
| Group 1 Group 2 Group 3|
|Item 1 blah blah blah | <---Pivot Table
|Item 2 blah blah blah |
|Item 3 blah blah blah |
|Item 4 blah blah blah | <---Where
Non-Pivot Values Were
|______________________________________|

Notice how the cells below it are now gone.

Is there some sort of simple solution to this problem, or will it involve a
more complex VBA work-around? If it does require some VBA, would anyone be
able to provide me with a sample of code that may work for my situation? I
would greatly appreciate any help!

Thanks in advance!!

 

[smartin]

Hi All,

I am trying to format a pivot table so that it doesn't overtake and delete
any information contained in cells that are below it. When I refresh my
table's data, it sometimes becomes longer than it was originally formatted to
be, and it deletes the information in the cells below it;

My POV and advice is, never mix pivot tables and non-pivot table data in
the same worksheet. It might work in a one-off situation, but if you
want a repeatable process, don't do it.

Whatever formulas you placed alongside the PT can likely be reproduced
in a separate worksheet using GETPIVOTDATA--without ever worrying about
how the PT's display range changes.

The file linked below crudely demonstrates a work flow beginning with
raw data, summarizing with a pivot table, creating a presentation-ready
summary (using GETPIVOTDATA), and finally, a chart.

http://vfdrake.home.comcast.net/files/excel/monthly_workflow_eg.xls