How do you create dynamic relative source data for Pivot Tables in Excel?

0
votes

I have a workbook that contains a bunch of pivot tables referencing different sheets in a separate workbook that I use as a template.

Every week I add to the pivot table workbook and save it in a separate folder.

When I try to add to the pivot table every week I have to update the source data for every single pivot table because it's referencing last week's template.

Example: In folder "45" I have pivot tables pertaining to my company for weeks 42-45 and the template with information pertaining to weeks 42-45. (I run reports four weeks at a time, there's too much data to keep an entire year...excel doesn't have enough lines for the information I'm dealing with)

I copy and paste that information into folder "46" add to the template so now it contains information 43-46, but the pivot table workbook doesn't update--I have to change the source data to every single pivot table to the template that contains weeks 43-46 in the "46" folder.

How do I make it so I don't have to keep updating the source data?

1
excelpivot-table
In regards to this: I copy and paste that information into folder "46" add to the template so now it contains information 43-46 Do you copy the info to a Workbook, that contains four weeks or one workbook per week? Please clarify. - EEM
If you store the data in a table, then when you cut and paste your new data into the table, you can just do a "refresh" on the pivot table and it should update it based on the new data. - Russ Thomas
@EEM One workbook per week and one pivot table workbook per week. So each week I basically delete a week and add a week. - CC29
@EEM For example -- Folder "45" I make a copy of it and just rename it "46," rename the "pivot table workbook", and the "data workbook" the pivot table work book is pulling from. Each folder has a pivot and data workbook with updated info. I drop a week and add the current week. I don't just keep one because there's too much information to store to keep all 52 weeks in a year. I also share the information with other people who are only concerned with 4 weeks at a time. - CC29
So folder 45 has four workbooks: two per weeks 42 to 45. Any reason for no having all the weeks in one folder, that will keep the connections for the prior 3 weeks and the template for the new week. Thus avoid the actual duplication (having one week in four folders) and the risk of one week getting outdated after an update in one folder, not been copied to the others. - EEM

1 Answers

0
votes

To change the "SourceData" of all pivot tables at once, open the workbooks containing the PivotTables & DataSource and save the DataSource workbook with the new name, using the "Save As" method.

Suppose we have:

Folder: C:\@SOs\@SO TEST\PivotTables SourceData - TEST\10.28 - 11.03 (44)
with workbooks: SOQ 64902342 PTs 44.xlsb (PivotTables) and SOQ 64902342 DATA 44.xlsb (SourceData)

  1. Option: Create new workbooks in same folder.
    • Copy PivotTables workbook and rename it SOQ 64902342 PTs 45.xlsb
    • To update the SourceData:
      • Ensure workbook SOQ 64902342 PTs 44.xlsb is not open.
      • Open SOQ 64902342 PTs 45.xlsb & SOQ 64902342 DATA 44.xlsb workbooks
      • Save SourceData workbook as SOQ 64902342 DATA 45.xlsb in the same folder
      • Save SOQ 64902342 PTs 45.xlsb workbook to preserve the updated SourceData.
    • Close SOQ 64902342 PTs 45.xlsb & SOQ 64902342 DATA 45.xlsb and move them to their new location.
      .
  2. Option: Copy the entire folder then update workbooks.
    • Copy folder 10.28 - 11.03 (44) and rename it as 11.04 - 11.10 (45)
    • In folder 11.04 - 11.10 (45) (PivotTables in workbook SOQ 64902342 PTs 44.xlsb have workbook SOQ 64902342 DATA 44.xlsb in folder 10.28 - 11.03 (45) as SourceData):
      • Rename the workbook SOQ 64902342 PTs 44.xlsb as SOQ 64902342 PTs 45.xlsb
      • To update the SourceData:
        • Open PivotTables & SourceData workbooks
        • Save SourceData workbook as SOQ 64902342 DATA 45.xlsb in the same folder.
        • Save SOQ 64902342 PTs 45.xlsb workbook to preserve the updated SourceData.
      • Delete workbook SOQ 64902342 DATA 44.xlsb.