[help] multiple IF formula; ancestor function?

Hi everybody!

I've been testing smartsheet for a month and I was just setting up a document by myself when I found a mistake in my own logic.

This is the formula I'm using: =IF(AND(ISBLANK([delivered by]@row); [start date]@row > TODAY()); "to do"; IF(AND(ISBLANK([delivered by]@row); [start date]@row <= TODAY(); [end date]@row >= TODAY()); "doing"; IF(AND(ISBLANK([delivered by]@row); [end date]@row < TODAY()); "delayed"; IF(ISDATE([delivered by]@row); "completed"))))

So my status column changes automatically from the relation of my date columns (start, end and delivered). But I need something that if a predecessor is "delayed", all the hierarchy/dependencies will be delayed too. I thought about using ANCESTOR, INDEX or MATCH but I didn't understand how to do that.

*This community is awesome! I got several tips from other discussions. I hope you can help me. Thanks.


  •  Hello,

    Currently we don’t have a method to reference and alter cells based on a specific rows predecessor actions but this will be considered as a possibility for future development.

    Have a wonderful day. Thank you for contacting Smartsheet Support.



    Smartsheet Technical Support

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!