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

pmcnamara

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")))))))))))))

Paul Newcome

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"))))))))))))

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.

pmcnamara

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"

pmcnamara

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

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"))))))))))))

pmcnamara

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

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"))))))))))))

pmcnamara

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

Paul Newcome

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

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

pmcnamara

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"