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.

easily pull data from many sheets into the 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.

The dashboard table

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 single formula that needs to be entered

=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 settings

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

copy a sheet to make a new one

Fill in the relevant milestone dates in the new sheet

set up 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

finished dashboard