Formula Help: Show Status looking left to right, of what milestone cells show

Have a HUGE team smartsheet that several departments update other sheets and feed to this one. Want to have a status column based on the condition of milestone cells (will skip some minor ones) to show where we are as a team. So only one status should show. Thanks in advance!

This is what I have so far:

=IF([Date Approved]@row = "", "Not Selected", "Selected", IF([Date Sent]@row="", "Sent", IF([Status]@row NOT([Status]@row = "Fully Executed"), "Pending", IF([Budget Status]@row NOT([Budget Status]@row = "Fully Executed"), "Pending Budget", IF([Contract Finalized]@row ="", "w/ Legal", IF([Reg]@row ="","Reg", IF([Approval]@row =""," Waiting on Approval", IF([Kits]@row ="", "Need to send Kits", IF([Binder]@row ="", "Need to send Binder", IF([Training and Access]@row NOT([Training and Access]@row = "Yes"), "Pending Training", IF([Attended]@row NOT([Attended]@row = "Yes"), "Pending", IF([Scheduled Date]@row NOT([Scheduled Date]@row = "" OR("NA")), "Pending", IF([Activation Date]@row ="", "Pending Activation Date", "Activated")))))))))))))

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF([Date Approved]@row = "", "Not Selected", IF([Date Sent]@row <> "Fully Executed", "Pending A", IF([Budget Status]@row <> "Fully Executed", "Pending Budget", IF([Contract Finalized]@row <> "Fully Executed", "w/ Legal", IF(Reg@row = "", "Reg", IF(Approval@row = "", "Waiting on Approval", IF(Kits@row = "", "Waiting On Kits", IF(Binder@row = "", "Waiting on Binder", IF([Training and Access]@row <> "Yes", "Pending Training", IF(Attended@row <> "Yes", "Pending Attendance", IF(NOT(ISDATE([Scheduled Date]@row)), "Pending B", IF([Activation Date]@row = "", "Pending Activation"))))))))))))

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    @pmcnamara

    There are several syntax errors in your formula. I’m more than happy to help you fix them but I need a little bit of information. If you don’t mind would you mind listing every condition and status you want. Like your very first If statement has a true and a false on it. The false would have to be moved to correct the formula. I think it would be easier to completely rebuild the formula from the conditions you want. Then To assume what your trying to do from the current formula.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Thanks, I tried to make it make sense. . hope this is clear.

    [Date Approved]@row if blank, report "Not Selected", if not blank, move to next milestone cell

    [Date Sent]@row is blank or not "Fully Executed", report "Pending A", if a date, move to next milestone cell

    [Budget Status]@row is blank or not "Fully Executed", report "Pending Budget", if "Fully Executed", move to next milestone cell

    IF([Contract Finalized]@row is blank , report "w/ Legal", if "Fully Executed", move to next milestone cell

    IF([Reg]@row is blank, report "Reg", if not blank, move to next milestone cell

    IF([Approval]@row is blank, report "Waiting on Approval", if not blank, move to next milestone cell

    IF([Kits]@row is blank, report "Waiting on Kits", if not blank, move to next milestone cell

    IF([Binder]@row is blank, report "Waiting on Binder", if not blank, move to next milestone cell

    IF([Training and Access]@row does not show "Yes", report "Pending Training", if "Yes", move to next milestone cell

    IF([Attended]@row does not show "Yes", report "Pending Attendance", if "Yes", move to next milestone cell

    IF([Scheduled Date]@row does not have a date, report "Pending B", if there is a date, move to next milestone cell

    IF([Activation Date]@row is blank, report "Pending Activation"

  • I am hoping for only one status, depending on what milestone triggers the first response. I.E. If The first three milestones cells are not blank, however [Contract Finalized] is blank; The cell will only show a status of " w/Legal "

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF([Date Approved]@row = "", "Not Selected", IF([Date Sent]@row <> "Fully Executed", "Pending A", IF([Budget Status]@row <> "Fully Executed", "Pending Budget", IF([Contract Finalized]@row <> "Fully Executed", "w/ Legal", IF(Reg@row = "", "Reg", IF(Approval@row = "", "Waiting on Approval", IF(Kits@row = "", "Waiting On Kits", IF(Binder@row = "", "Waiting on Binder", IF([Training and Access]@row <> "Yes", "Pending Training", IF(Attended@row <> "Yes", "Pending Attendance", IF(NOT(ISDATE([Scheduled Date]@row)), "Pending B", IF([Activation Date]@row = "", "Pending Activation"))))))))))))

  • Perfect! is there a way to adjust "Pending B" equation to be true if "no date" or "NA"?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That would look like this:

    =IF([Date Approved]@row = "", "Not Selected", IF([Date Sent]@row <> "Fully Executed", "Pending A", IF([Budget Status]@row <> "Fully Executed", "Pending Budget", IF([Contract Finalized]@row <> "Fully Executed", "w/ Legal", IF(Reg@row = "", "Reg", IF(Approval@row = "", "Waiting on Approval", IF(Kits@row = "", "Waiting On Kits", IF(Binder@row = "", "Waiting on Binder", IF([Training and Access]@row <> "Yes", "Pending Training", IF(Attended@row <> "Yes", "Pending Attendance", IF(OR(NOT(ISDATE([Scheduled Date]@row)), [Scheduled Date]@row = "N/A"), "Pending B", IF([Activation Date]@row = "", "Pending Activation"))))))))))))


  • It's still showing "Pending B" when there's N/A in the cell?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Right. That's what you asked for isn't it?

    ""Pending B" equation to be true if "no date" or "NA""

  • On second thought, it should be if there's a past date or NA, it would move to the next milestone. If blank or future date "Pending B"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!