+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Change pivot table source data for multiple pivot tables simultaneously

  1. #1
    Registered User
    Join Date
    06-08-2007
    Location
    Canada
    MS-Off Ver
    Office 2007
    Posts
    12

    Change pivot table source data for multiple pivot tables simultaneously

    Hi there,

    I have many workbooks that each contain many pivot table reports ..often as many as 20 or 30 in a workbook. Most are reports for diffent people with different needs, but all reading from the same underlying data.

    Frequently there is a need for an additional field to be added to help the pivot tables zero in on selected conditions.

    The problem is if a field is added at the end of the others, it seems one has to go into each pivot table individually and redefine the source data in each. I realize if I 'insert' a new field in the source data the pivots will include that, but that doesn't work because often the source data is 'downloaded' from other mainframe files and the new column needed at the end is based on a formula or lookup.

    Any way around having to do each one independently?

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Change pivot table source data for multiple pivot tables simultaneously

    If you could add the new column WITHIN the existing sourse data range, when you refresh the PT, the new column will be available in the field list.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-08-2007
    Location
    Canada
    MS-Off Ver
    Office 2007
    Posts
    12

    Re: Change pivot table source data for multiple pivot tables simultaneously

    Yes, as mentoned 'I realize if I 'insert' a new field in the source data the pivots will include that', but the problem is the new field has to go at the end. Other workbooks may also be looking up data there and if I insert columns it changes the lookup offset and I'd have to find all those lookups and modify their formulas.
    Much simpler to include it at the end (other than having to expand the lookup range for any report needing it).

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Change pivot table source data for multiple pivot tables simultaneously

    If they all use the same data, then if you base all the pivot tables off one pivot table, there will be only one data cache - if you update that and refresh the other tables, they should pick up the new data automatically. You could also use a dynamic named range as the data source for all the pivots.
    Good luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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