We have two excel workbooks:
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.
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).
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
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)?