The whole idea of a Dashboard is to provide a graphical interpretation of the most relevant data, such as a balanced scorecard. Moreover, the updating of new data into the dashboard every week or month should be automated as much as possible. This is where the data model becomes crucial if you want to achieve this.
If you get the data model correct at the beginning, the more successful any dashboard will be. The easiest way to understand this concept is to ask – how will my performance report be updated with new data?
Pivottables provide an excellent way to dynamically update dashboard charts and tables when new data appears. Pivottables should be your starting point for a data model. New data is added to the source data list for the pivottable. These pivottables are then hidden in staging worksheets. Through the use of excel functions, links and VBA we can update pivottables when new data is added, which will subsequently show in the reports.
For example, When the pivottables are updated using the refresh button, the various pivottables within the staging worksheet are updated, which consequently shows in all the other dashboards components.
Links from pivottables to individual cells within a dashboard report can be done using the ‘getpivotdata’ formula. This needs to be switch on in order for this to work. If it is switched off, then all you get is a direct link to a cell rather than a dynamic link.
If i wanted a cell to show quarters 1,2,3 and 4 when the data appears, i would use the getpivotdata to show this. This means if i were to change the layout of the pivot, the cells would still show the value of quarters 1,2,3 and 4, but if i did not use the getpivotdata function, then i would only get the value of the cell – which is now likely to be something different now after the pivottable has been pivotted.
It is simple to use. Select the cell and type in the = sign. Then point to the value in the pivottable you want the cell to link to.
TIP: If this doesn’t work then click on the generate get pivotdata icon to switch the formula back on.