Formula returning a blank cell

Anna.Hogan
Anna.Hogan ✭✭
edited 12/09/19 in Formulas and Functions

I have been trying to figure this out for hours with no luck. We've created a formula to automatically update our status column in our project plan sheets. We have specific parent rows that are designated as milestones. The milestone row status cells should update based on the status of the child rows. A milestone row is indicated in the Milestone? row by clicking the symbol (star) to fill it in. I've got the formula below to guide automated status updates. In our milestone rows, the formula is returning a blank cell, and I cannot for the life of me figure out why. I've searched around in the Community and haven't seen a similar question/response. 

 

=IF([Milestone?]@row = 0, IF(Blocked@row = 1, "Blocked", IF(ISBLANK([Actual Start Date]@row), "Not Started", IF([Actual Completion Date]@row <> "", "Completed", IF([Projected End Date]@row < TODAY(), "Late", IF(NETWORKDAYS(TODAY(), [Projected End Date]@row) <= 2, "Deadline Approaching", IF([Actual Start Date]@row < TODAY(), "In Progress", "")))))) + IF([Milestone?]@row = 1, IF(COUNTIF(CHILDREN(), "Blocked") >= 1, "Blocked", IF(COUNTIF(CHILDREN(), "Late") > 1, "High Risk", IF(COUNTIF(CHILDREN(), "Deadline Approaching") >= COUNT(CHILDREN()) / 2, "Low Risk", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Not Started")))))))

smartsheet.JPG

Comments

  • Mike L.
    Mike L. ✭✭✭
    edited 04/10/19

    One of your IF statements returns a blank cell if false.  What if you change the statement from: 

    IF([Actual Start Date]@row < TODAY(), "In Progress", ""   

    to

    IF([Actual Start Date]@row < TODAY(), "In Progress", "you are here"

    If that doesn't produce the new message in the cell I would start over by testing each condition to see if you get the intended result.  Probably the conditional statements before and after the "+" symbol, each time testing one component then rebuild the formula. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot that covers all of the column referenced in your formula for the row in question? That may help us narrow down where an issue could be hiding.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!