Only Show Tasks with Completed Predecessors in Report

chad.jackson73431

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.

Mike Wilday

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 they tend 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.

chad.jackson73431

Awesome. Thanks so much. I think this will work!

Mike Wilday

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.

Paul Newcome

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.

chad.jackson73431

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?

Mike Wilday

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. 

 

chad.jackson73431

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?

 

Paul Newcome

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")

chad.jackson73431

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.

chad.jackson73431

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.

Tanner Polzin

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/