Pivot Table - how to prevent Refresh overwriting rows below the Table

[thunt]

I have a Pivot Table based on data that gets imported.

The number of rows in the Pivot Data varies depending on the underying
data.

Below the Pivot Table are some calculations and then a couple more
Pivot Tables.

I'm building a Profit & Loss report and each Pivot table summarises the
relevant report sections (e.g. Sales Pivot Table, Cost of Sales Pivot
Table).

The problem is, when the pivot table refreshes, it can overwrite
formulas and tables below it if the table increases in rows.

Any ideas/suggestions please to prevent this.

Thanks

T Hunt

 

[Hall]

I don't know if you can prevent that with refreshed pivot tables. There is
more control in this regard when refreshing an external data query.

However, I suggest staggering your steps using sheets. If you create
separate sheets for each pivot table and/or data query, then have a separate
sheet again for the final report page that puts them and formats them
altogether. The cells in that sheet can have absolute references to the
cells in the other sheets so that whatever they get updated with would
appear in the final report. You'd have to make the format accommodate
different amounts of rows to scale to your pivot table sizes.

Hope this gets you on a workable track.

 

[thunt]

Thanks, I've tried that approach, but still run into problems when
trying to bring everything together on one worksheet due to the
variable number of rows.

 

[Roger Govier]

Hi Tim

I bring all my data from Sales Ledger and Purchase Ledger together into one
sheet, and do a PT on that.
My report is on a separate sheet altogether, and uses the GetPivotData
function based on the column Name (month) and row name (Nominal Code).
That way, it doesn't matter where in the row range the Nominal code is, it
is found by GetPivotData.

Regards

Roger Govier

 

[thunt]

Thanks Roger,

I think I've tried that, but without success so must be doing it
differently.

When you assmble the data on the separate sheet, is it working to a
fixed list of nominal accounts? That would work for me, but what I'm
trying to accomodate is automatically including on the report any
additional nominal accounts that may occur.

 

[Roger Govier]

Hi Tim

Trying to hit an ever moving target, eh!!!
No, in the case I refer to the Nominal code list is fixed. If there are
further additions, then the report has to be modified to pick those up.

The client concerned would post additional items to a Suspense account
(included in the report). On the next routine visit (usually quarterly), I
would agree with them the addition of the new nominals, and journal from
suspense to the appropriate nominal.

Regards

Roger Govier