Background:

We have two excel workbooks: Data and Visual which we are using to track and visualize activities in our project. The Data file is organized like a database: each sheet is a table (which helps with slicing). One of the sheets is called Activities, which has each activity, which team is performing it, start / end dates, environments, etc.

The Visual file has an underlying PowerPivot, which pulls the data from the Data file and performs calculations (ex. WeekStart, DayOfWeek, etc.), formatting, etc., adding extra columns to the data model. This model is then used to power multiple pivot charts and a single pivot table.

The pivot charts display different views of the activities.

The pivot table organizes the data by week, so that we can track on a day-to-day basis the activities, updating end dates, and adding comments. (We understand that this can be done in MS Project, but Project does not have the flexibility that slicers bring when trying to find conflicting activities).

Question:

We would like to update the data directly in the Visual pivot table and have it update that information in the Data file. We understand that pivot tables are meant for only viewing the data, but are looking to find a way of having two-way updates between the Data file and the Visual file.

In exploring solutions, we found this hyperlinking option, but it seems very cumbersome and error prone. Is there any other option? Could we change the file structure to have this work (have 1 file instead of 2 files)? Is there something that can be done with different data models (like having the data in Access or some other DB vs. and Excel workbook)?

  • I guess my question is - why do you want to update the pivot table and have that sync to the source? Why not just update the source? Maybe if you explain why you're doing this we can give you better answers – Raystafarian Aug 6 '15 at 16:16
  • @Raystafarian We are creating a deployment playbook and a release management tracker. The tracker is more granular than a project plan, and the playbook items are very detailed, hour-to-hour tasks. We want to be able to project the playbook as a pivot table, so we can slice as needed for various teams, but we also want to be able to make adjustments to the items without having to switch back and forth between the pivot and the source. – rishimaharaj Aug 7 '15 at 16:59
  • @Raystafarian We are also trying to put it all within one workbook, so the original source data, data models, and pivots will be together. – rishimaharaj Aug 7 '15 at 17:02
  • I'd say this is probably possible with VBA, but not the best way to do it. First - I assume you'll update the pivot table from a source range? Not necessarily in the original book. So you could grab any changes as they occur and pass them back to the original book. – Raystafarian Aug 7 '15 at 17:12

I would use the Power Query Add-In for this. It can read data from an Excel file (e.g. your comments) and add that as a Table in your Data file.

You could then include that new table in your Power Pivot model.

You can also load data directly from Excel via Power Query into a Power Pivot model.

  • Thanks @Mike. While this is a different way of loading the data, it still ends at the same point -- can't update the original source data from the pivot. Any ideas on how to tackle that part? – rishimaharaj Aug 7 '15 at 17:01
  • You can update the data (row or column label cells) in the pivot. Then a Power Query can read the Pivot Table as a table, and combine it with your existing data. – Mike Honey Aug 10 '15 at 0:26

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.