Building a Dashboard off of my Status column

Ty Werven

So I am want to build a dashboard that will track/show how many complete, not started, and in progress tasks I have. I have four separate sheets that I will be doing this with. Each having about 8-12 tasks that will be tracked. The problem is which I will share below is under each "Task" I have a Indent where committees will be adding in their own information as we go, so typically I would just make a report a use a widget to count per breakdown of status.

So what I mean is the action steps that they make will also have an option to be complete, In progress, or not yet started. But I only want to track the status of my broad over arching tasks, not their own individual plan on how to fill the task if that makes sense? Basically, the rows across in grey will not matter in the widget, just the top row has a white task column.

Is it possible to filter out these action step rows in a report? Or should I make a metric sheet that counts if by the task column name and if it is complete not yet started or in progress?

Would like to hear other opinions. Thanks!

Kerry St. Thomas

The answer, as I see it, is a Hierarchy column. I use =COUNT(ANCESTORS()) in my Hierarchy column - 0 would be the top level, and 1 would be the indented Action Steps; if substeps are added, they'd be 2 …and so on. You could then create a report filtering to Hierarchy = 0, or create a KPI sheet that collects info that factors in only level 0.

I use this idea for A LOT of things - standardizing a conditional format so people can see the indent level, creating reports and KPI sheets, and so much more. Helper columns definitely live up to their name. :) :) :)