+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    thunt
    Guest

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

    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


  2. #2
    Hall
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    thunt
    Guest

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

    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.


  4. #4
    Roger Govier
    Guest

    Re: Pivot Table - how to prevent Refresh overwriting rows below theTable

    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 wrote:
    > 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.
    >


  5. #5
    thunt
    Guest

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

    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.


  6. #6
    Roger Govier
    Guest

    Re: Pivot Table - how to prevent Refresh overwriting rows below theTable

    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


    thunt wrote:
    > 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.
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1