We use cookies to give you the best experience on our website. If you continue without changing your settings, we’ll assume that you are happy to receive all cookies on this website. However, you can change your cookie setting at any time. x
Home > Blog

Automatically pulling data from other sheets into a dashboard

Situation

A client had a single worksheet per project and wanted to pull data from each sheet into a single dashboard to give a view across the programme.   They wanted to be able to add or remove sheets without the hassle of linking individual cells.

All the project sheets had the same format, and they wanted to be able to pull a standard set of milestone dates and the RAG status for these into the dashboard.

 

Displaying data from many sheets in a dashboard

Solution

The milestone dates were displayed in a table.  Down the left-hand side was the list of projects.  The names of the projects were exactly the same as the worksheet names.

a simple dashboard of milestones across many projects

The INDIRECT function was used to create a formula that pulls the data from each project sheet into the summary table.

The same technique was used to drive the conditional formatting, which displays the RAG status shown in each project sheet.

Setting up the report

Absolutely key to this method is that the names in the table are the same as the sheet names; even an extra space will defeat the INDIRECT method.

The project sheets are a copy of each other so that the source data is always in the same cells.

The table is set up in the dashboard sheet with the project names (and hence sheet names) down the left and the milestone names across the top.   Row 1 contains the column letters for each milestone.

The following formula is placed in cell B3.  The formula is designed to be entered once and then copied across all the milestones and projects.

the INDIRECT formula to combine data from many sheets into one

=IF($A3="","",IF(INDIRECT("'"&$A3&"'!"&B$1&"3")="","",INDIRECT("'"&$A3&"'!"&B$1&"3")))

The INDIRECT statement breaks down as follows:

INDIRECT( “ ‘ “ & sheet name & “ ‘ !” & column letter & “3”).

The formula in B3  resolves to = ‘ Project A’!B3

The conditional formatting uses the same INDIRECT approach with one variation; it is looking at Row 4 for the name of the RAG to be applied

Conditional formatting driven by other sheets using the INDIRECT formula

The easiest way to enter the four conditional formatting is to complete one fully (for instance, the “complete” one and then duplicate it and edit the “Complete” in the formula to “Red” and so on.

Once cell B3 is completed, the rest of the table is filled in by copying and pasting B3 overall milestones and projects.

Adding additional sheets or projects

Add a new sheet by copying one of the existing project sheets

adding a copy of a sheet to the workbook

Fill in the relevant milestone dates in the new sheet

 

fill in the details for the new project

Add the new sheet name to the list of projects in the dashboard

Copy the last row of calculations and formats down to cover the new line

a simple copy down fills in all the details for the new project

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.