Values across different tasks for Dashboard?

Arthur A

Hi,

I have a big sheet (called "Test")that have a lot of tasks that have the same type and number of subtasks.  All subtasks have an "assigned to" column (contact list) and all subtasks are among three typs: not started, in progress or complete.   I created a separate sheet (called Test.metrics) that roll up all "assigned to" info and "status" info for all subtasks, and plan to use that to build a dashboard -- how many tasks are assigned to different owners and how many % of subtasks are at different status etc.  I know we can use COUNTIF in the metrics page to pull data from Test, but encountered issues when trying to pull data from the "assigned to" column or "status" column since they are across different subtasks.  

Any suggestions are welcome!

AA

Shawn Connelly

It's not really clear what issue you are having. I don't think whether the task is a subtask or not should matter in terms of your formula. Are you receiving an error message? What is the complete formula you tried to use?

Here's an example of how I would create the calculation. The first part is the cross-sheet reference, and the second part can either be the value that you are counting, or a cell reference within your test.metrics sheet that has the exact same text as what you're counting.

=COUNTIF({Test Range 1},"John Doe")

Mike L.

Since you have already designed a pre-production test page maybe you can make copies that are public and share the link? 



 

Paul Newcome

Are you trying to break it down by module? Are you trying to select a specific range of cells instead of the entire column in your cross sheet references?

 

If you are not breaking it down by Module, then

=COUNTIFS({Test Sheet Assigned To Column}, [Primary Column]@row)

should work for the names and

=COUNTIFS({Test Sheet Status Column}, [Primary Column]@row)

should work for the Statuses.

.

If you ARE trying to break it down by module, there are two ways to do it. The first is to select only the child rows when establishing the range for the cross sheet reference. This is not the advised way.

 

If you are breaking it down by module, I suggest adding a text/number type column to the Test sheet (I'll call it Parent for this example).

In this column you would simply use the formula of 

=PARENT([Task Name]@row)

 

You would then include that in your COUNTIFS formulas on your Metrics sheet.

=COUNTIFS({Test Sheet Assigned To Column}, [Primary Column]@row, {Test Sheet Parent Column}, "Module 1")

=COUNTIFS({Test Sheet Status Column}, [Primary Column]@row, {Test Sheet Parent Column}, "Module 1")

Arthur A

Hi, Shawn,

This formula works, but I wanted to exclude some tasks from the calculation.  For example, I only want row 1 -- row 600 of the "assigned to" column included in the calculation.  Is there a way to do that?  I know you can click and drag in Smartsheet, but I'm a keyboard shortcut junkie. 

Thanks!

AA

Paul Newcome

The only way to specify a range when using cross sheet references is manually. If you don't want to click and drag, your only other option would be to use your arrow keys to get to the first cell then hold down shift and use other keys to select the rest of the range (page down, arrow keys, etc.). There isn't really a "shortcut" for this part of formula building.

Arthur A

Paul,

That's very helpful. Thank you!

AA

 

Paul Newcome

Happy to help.