Reference Predecessor Cell / INDIRECT Function

I'm using a formula to automate a status column that updates based on dates and/or a "Complete" checkbox.

The formula has to reference the task before and I've gotten that to work just fine if it's the literal task before.

My problem is that I'd like to adjust the formula so that it references a predecessor task (as indicated in the Predecessor column) rather than just the first task before.

For example, part of the formula contains if "Complete6" is checked. Rather than indicating explicitly in the formula "6", I'd like it to join together "Complete" with the number in the predecessor column. So if the formula is in row 10 and the predecessor is row 5, it changes to "Complete5" accordingly.

I'm thinking a helper of some sort is needed however I can't quite figure that part out. In other sheets, I could simple have a helper column and use the INDIRECT function such as:

INDIRECT("Complete" + [email protected])

TL;DR - I'd like to create the formula similar to INDIRECT.


Formula that works for cell/task directly before:

=IF([email protected] = true, "Complete", IF(ISBLANK([email protected]), "", IF([email protected] < TODAY(), "Late", IF(AND(Complete6 = true, [email protected] >= TODAY()), "In Progress", "Not Started"))))

How can I adjust this to essentially become "Complete" + Predecessor Row?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!