Combining IF Formula - Is This Possible?

Alex Beaupre

Hello, I'm working on a formula that will trigger a status of "complete" based on a Task Reviewed column.

=IF([Task Reviewed]5883 = 1, "Complete", PARENT())

I am trying to combine the formula with another one that will trigger the same status as the parent row when the cell above it is complete.

=IF(Status5882 = "Complete", PARENT(), "")

The end goal is to have the priority cascade to the next row when the task has been reviewed. The due date and task assigned check box are triggered based on the priority, which is based on the parent row and the time between column.

Can someone let me know if this is possible to have my cake and eat it too? Thank you!

Kelly Drake

@Alex Beaupre - Yes this is possible....

=IF(logical statement, result if logical statement true, IF(2nd logical statement, result if 2nd logical statement is true, result if neither logical statements are true)). Note that the formula will read left to right and if the first logical statement is true it returns the result and stops reading the formula. If you need both conditions to be true you need to nest an AND.

But since your result is the same for each logical statement your formulas would best optimize by nesting an OR for the conditions (If both need to be true swap in AND rather than OR)

=IF([Task Reviewed]@row = 1, Status@row = "Complete"), "Complete", PARENT())

(Side note - a best practice is to use @row instead of the absolute reference of the row numbers: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell)

I'm sure you can get to the automation you're looking for with the right logical path/formula - but what column are you putting this formula into? This formula won't work if you're trying to put it into the Status column.

If the rows are supposed to occur in a specific order, you'll need to add that as a data point by just counting starting from 1 (for the formula below I titled the column Task Order) and then I think you can nest something like this into the status

= IF([Task Reviewed]@row = 1, "Complete", IF(MIN(COLLECT([Task Order]:[Task Order], [Task Reviewed]: [Task Reviewed], 0))= [Task Order]@row, PARENT(), ""))

This formula should copy all the way down your status column and will automatically update as you check the Task Reviewed column.

Kelly Drake

@Alex Beaupre great to hear! It should work if you expand all and then you'll need to fill in the task order column as well. If the projects labeled as 1 in Task Order equal the Parent role that's working as expected.

If you want to run projects simultaneously we might have to look at modifying the way the MIN/COLLECT segment works so that it reviews/collects the task order for the same group fo children rows.

If your children rows are consistently the exact same - you could modify the formula I gave to this and hopefully that would work:

= IF([Task Reviewed]@row = 1, "Complete", IF(MIN(COLLECT([Task Order]:[Task Order], [Task Reviewed]: [Task Reviewed], 0, Project:Project, Project@row))= [Task Order]@row, PARENT(), ""))

Kelly Drake

@Alex Beaupre - Yes this is possible....

=IF(logical statement, result if logical statement true, IF(2nd logical statement, result if 2nd logical statement is true, result if neither logical statements are true)). Note that the formula will read left to right and if the first logical statement is true it returns the result and stops reading the formula. If you need both conditions to be true you need to nest an AND.

But since your result is the same for each logical statement your formulas would best optimize by nesting an OR for the conditions (If both need to be true swap in AND rather than OR)

=IF([Task Reviewed]@row = 1, Status@row = "Complete"), "Complete", PARENT())

(Side note - a best practice is to use @row instead of the absolute reference of the row numbers: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell)

I'm sure you can get to the automation you're looking for with the right logical path/formula - but what column are you putting this formula into? This formula won't work if you're trying to put it into the Status column.

If the rows are supposed to occur in a specific order, you'll need to add that as a data point by just counting starting from 1 (for the formula below I titled the column Task Order) and then I think you can nest something like this into the status

= IF([Task Reviewed]@row = 1, "Complete", IF(MIN(COLLECT([Task Order]:[Task Order], [Task Reviewed]: [Task Reviewed], 0))= [Task Order]@row, PARENT(), ""))

This formula should copy all the way down your status column and will automatically update as you check the Task Reviewed column.

Alex Beaupre

@Kelly Drake Thank you so much! That worked like a charm, but only seems to work for one parent / child relationship... We manage a variety of projects on the sheet, and each task is a child to the parent (project) row. As soon as I copy the formula to another project and set the task order, the formula no longer works.

I've tried both keeping the task order consistent (1-50 for each task for each project), as well as having a unique task order for each task. Neither seem to do it.

Wondering if there is a work around for that, or perhaps I'm doing something incorrectly. Here's a larger screenshot of what we've got going on. Again, appreciate the help.

Kelly Drake

@Alex Beaupre great to hear! It should work if you expand all and then you'll need to fill in the task order column as well. If the projects labeled as 1 in Task Order equal the Parent role that's working as expected.

If you want to run projects simultaneously we might have to look at modifying the way the MIN/COLLECT segment works so that it reviews/collects the task order for the same group fo children rows.

If your children rows are consistently the exact same - you could modify the formula I gave to this and hopefully that would work:

= IF([Task Reviewed]@row = 1, "Complete", IF(MIN(COLLECT([Task Order]:[Task Order], [Task Reviewed]: [Task Reviewed], 0, Project:Project, Project@row))= [Task Order]@row, PARENT(), ""))