Only Show Tasks with Completed Predecessors in Report (2024)

Options

chad.jackson73431 ✭

12/26/18 edited 12/09/19 in Smartsheet Basics

Hey folks. We have a number of projects with the right dependencies set up. We have reports for all our people to use. However, they're seeing tasks before those task's predecessors are complete. That's causing them to start tasks before the tasks are ready to be started.

Is there a way to only show tasks with predecessors that have been completed or that have no predecessors?

Under the 'What' option in the Report Builder, I found the option to include Predecessors, but do I specify it 'contains' a *? Advice would be greatly appreciated.

Tags:

Reports

1 · Share on FacebookShare on Twitter

Mike Wilday ✭✭✭✭✭✭
12/26/18

Options
Hi Chad,
We had a similar need as user reports never indicated whether the task was ready for them to begin. I don't know if you have a template for your projects and theytend to be the same for each project, but what I ended up doing was creating a new column that I added to reports that showed when the predecessor's task was complete or not. I added an IF function to check the rows of the predecessors to ensure that the task was finished and then left a statement indicating its the current tasks status.
For example. IF(Status3 <>"Complete", "Task3 not Complete", "Ready to begin")
I would put this in the new column for row 4 to check if row three was complete.
It got a little more complicated when multiple items were required to be complete before a task began. Hopefully, this will give you a start.

0 · Share on FacebookShare on Twitter
chad.jackson73431 ✭
12/26/18
See Also
Functions for predecessors How To Copy And Paste Smartsheet With Predecessors

Options
Awesome. Thanks so much. I think this will work!
0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
12/26/18

Options
Cool. Let me know if you have any questions. We manage multiple projects on the same sheet and have a template of sorts for each type of project. Each designed similarly.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/27/18

Options
Two very big "if's", but IF your predecessors are Finish to Start, and IF you have few enough per row to be able to parse out...
It is possible to use an IF(INDEX(............ statement to reference the predecessors's status. It can get a little convoluted if you have more than one or two predecessors, but if you are able to break it down into smaller sections, it is possible to reference the status of the predecessors without having to write very specific, individual formulas for each row.

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on FacebookShare on Twitter
chad.jackson73431 ✭
01/13/19

Options
Hey folks! This has been tremendously helpful so far. I think I have it work as I intended.
Quick question though. Right now, I have hardcoded a new cell to look at a specific predecessor's cell. If I change the predecessor, however, this cell will not look at the completion of that predecessor. Here is the formula I'm using.
=IF([Done?]2 = 1, "Yes", "No")
I actually want to replace the 2 with 'Predecessors@row', but when I do that, Smartsheets states it is unparseable. How do I get this to return a numerical value?

0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
01/14/19

Options
Hi Chad,
I'm not sure I understand your question. Can you post a screenshot of the specific issue/problem?
Some things to note: use @row for indicating the row number of the current row that the formula lives on. If you are trying to get information from a different row, you will have to use the specific row number. OR, if you are using indented rows, you can use some formulas like Children().
For more on @row see https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
For more on the =Children() function see: https://help.smartsheet.com/function/children
It's difficult to direct you without seeing what you are trying to do.
0 · Share on FacebookShare on Twitter
chad.jackson73431 ✭
01/14/19

Options
No problem. Let me give you a specific example. See the screenshot below for reference.
The task on row 10 has a predecessor task, row 9. I defined the following formula in the 'Ready?' column of row 10 to identify if that predecessor task has been completed or not.
=IF([Done?]9 = 1, "Yes", "No")
This works. However, what if I change the predecessor task of row 10 to row 7 instead? I would change the 'Predecessors' column. But the formula still points at row 9.
To fix this problem, I wanted to use grab the numerical value of the 'Predecessor' of the current row. That would then be used for the formula above. I tried the following...
=IF([Done?](Predecessor@row) = 1, "Yes", "No")
... but it comes up 'Unparseable.'
Does that clarify my request?

0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/14/19

Options
You'll need to use an index formula to be able to pull the yes/no value for the predecessor. Something along the lines of
=IF(INDEX(Done:Done, VALUE(LEFT(Predecessors@row)) = 1, "Yes", "No")

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on FacebookShare on Twitter
chad.jackson73431 ✭
01/18/19

Options
Hey folks. I am sure that the INDEX route would work, but I am getting errors for everything I try there. I would like to go with the simpler solution here. I would like to go from something like this...
=IF(A5 = 1, "Yes", "No")
... to something like this...
=IF(A(VALUE(LEFT(B@row))) = 1, "Yes", "No").
When I try this, I get an #UNPARSEABLE error. Is there something special I need to do to get this to work?Is it not possible to use a function to determine a row value?
That is one of the fundamental things in Excel.

0 · Share on FacebookShare on Twitter
chad.jackson73431 ✭
01/21/19

Options
Alright. I have this check working.
It evaluates if a predecessor's task is complete.
=IF(ISBLANK(Predecessors@row), "Start (No Predecessor)", IF(INDEX(TaskCheck:TaskCheck, VALUE(LEFT((Predecessors@row), 2))) = 1, "Start (Predecessor Complete)", "Don't Start (Predecessor Incomplete)"))
Predecessors is the standard Smartsheets predecessor column.
TaskCheck is the standard Smartsheets checkbox column.
It includes the logic that if there is no predecessor, then the task can be started.
There is no error checking currently.

0 · Share on FacebookShare on Twitter
Tanner Polzin ✭✭✭
02/02/21

Options
I came up with my own solution for indicating if all predecessors are finished. You can see my solution here: https://community.smartsheet.com/discussion/75593/formula-to-indicate-a-task-is-ready-because-predecessors-are-complete-solution/

0 · Share on FacebookShare on Twitter

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });