ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
How it works
3
4
In practice: add a task to Task Data, set its Status, and the numbers in "At a glance" and the percentages in "Progress by workstream" on the Dashboard update on their own — no manual typing required.
5
6
Color legend
7
ColorMeaning
8
Cell you're meant to edit by hand
9
On track / Done
10
At risk / In Progress
11
Behind / Blocked
12
To Do
13
14
What to edit by hand (highlighted in yellow)
15
On the Task Data sheet: (add one row per task)
16
Workstream: type the name exactly as it appears in the Dashboard table (formulas match by exact text, so a typo breaks the match)
17
Status: pick from the dropdown: To Do / In Progress / Done / Blocked. The cell fills with the matching color automatically
18
Owner: the person responsible
19
20
On the Dashboard sheet:
21
Project and Snapshot date: type these in by hand. The date is intentionally not automatic (not =TODAY()) — a snapshot should capture a fixed moment in time, not "right now"
22
Workstream Status (On track / At risk / Behind): also a dropdown, but this one is a judgment call, not a pure function of the percentage. Hover the cell for a comment with a rough guideline (≥70% done → On track, 50–69% → At risk, <50% → Behind) — the final call is still yours
23
Active blockers: free text; there's nothing to automate here by nature
24
25
What calculates itself
26
• Total tasks, Completed, In progress, Blocked, % complete in "At a glance" — COUNTA/COUNTIF pulling from Task Data
27
• Progress % per workstream — COUNTIFS (tasks done ÷ total tasks for that workstream)
28
• Owner per workstream — pulled automatically from the first matching task on Task Data (INDEX/MATCH)
29
30
Adding a new workstream
31
1. Type its name into a new row in the "Progress by workstream" table on Dashboard (column A)
32
2. Copy the formulas from a neighboring row into the Progress (C) and Owner (D) columns
33
3. Add tasks with that same workstream name on the Task Data sheet
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100