Multiple IF/AND for duration and status

Alessandro Terranova

Hi Community,

I am trying to structure an IF/AND formula to reflect the following conditions:

IF [Duration]@row <> 0, AND IF [Complete]@row =0, "Not Started"

IF [Duration]@row <> 0, AND IF [Complete]@row =0, AND IF[Complete]@ previous row = 1, "In Progress"

IF [Duration]@row <> 0, AND IF [Complete]@row =1, "Complete"

IF [Duration]@row = 0, "No Task"

Could somebody kindly help? Hope the above makes sense.

Thank you!

Joanna Waligora

Once an IF condition is met, the remainder are not evaluated, so some of the above complexity can be reduced with sensible ordering of arguments.

As far as I can tell, Smartsheet doesn't offer relative cell references (some additional discussion on row number tracking is available here: https://community.smartsheet.com/discussion/6757/formula-to-determine-row-number).

Consequently, you will need to replace the "X" below with the actual row number you want to reference; it should still update fine when the formula is copied. Another option you may wish to look into is the PARENT() formula, which will apply if you have a parent-child/task-subtask relationships defined in your dataset.

IF([Duration]@row = 0, "No Task",

IF( [Complete]@row =1, "Complete",

IF([Complete]X =1, "In Progress",

"Not Started" )))

Alessandro Terranova

Thank you Joanna, I ended up using:

=IF([Actual days]@row = 0, "No Task", IF(Complete@row = 1, "Complete", IF(Current@row = "Current", "In Progress", "Not Started")))

Where Current is (for row20, in this case):

=IF(AND(Complete19 = 1, Complete20 = 0), "Current", "Not current")

Best

Joanna Waligora

Once an IF condition is met, the remainder are not evaluated, so some of the above complexity can be reduced with sensible ordering of arguments.

As far as I can tell, Smartsheet doesn't offer relative cell references (some additional discussion on row number tracking is available here: https://community.smartsheet.com/discussion/6757/formula-to-determine-row-number).

Consequently, you will need to replace the "X" below with the actual row number you want to reference; it should still update fine when the formula is copied. Another option you may wish to look into is the PARENT() formula, which will apply if you have a parent-child/task-subtask relationships defined in your dataset.

IF([Duration]@row = 0, "No Task",

IF( [Complete]@row =1, "Complete",

IF([Complete]X =1, "In Progress",

"Not Started" )))

Alessandro Terranova

Thank you Joanna, I ended up using:

=IF([Actual days]@row = 0, "No Task", IF(Complete@row = 1, "Complete", IF(Current@row = "Current", "In Progress", "Not Started")))

Where Current is (for row20, in this case):

=IF(AND(Complete19 = 1, Complete20 = 0), "Current", "Not current")

Best