Updating Status column based on two different columns

Amanda St John

I am looking for a way to have a column state "Cancelled", Submitted, or "Completed" based on two different columns in Smartsheet. If SAP1 column = "No" I want the "Status" column to say "Cancelled". If SAP1 is "Yes" and another column that states who completed the request is filled in with any name (is not blank), then I want the status column to say "Complete". If SAP1 is "Yes" but the column that states who completed the request is empty than I want the status column to say "Submitted". As of right now I have two separate formulas that each do 1/2 of what I want but I have been unsuccessful in combining them to actually work together. What am I missing?

=IF(SAP1 = "No", "Cancelled", "Submitted")

=IF(ISBLANK([Entered By]3), "Submitted", "Completed")

David Tutwiler

You're looking for nested if statement. Should looks something like:

=IF(SAP1 = "No", "Cancelled", IF(AND(SAP1="Yes",ISBLANK([Entered By]3)),"Submitted",Completed))

David Tutwiler

You're looking for nested if statement. Should looks something like:

=IF(SAP1 = "No", "Cancelled", IF(AND(SAP1="Yes",ISBLANK([Entered By]3)),"Submitted",Completed))

Amanda St John

@David Tutwiler Yes, that worked! I must have been missing the "AND" statement.

=IF(SAP34 = "No", "Cancelled", IF(AND(SAP34 = "Yes", ISBLANK([Entered By]34)), "Submitted", "Completed"))

David Tutwiler

Ah! Perfect. Glad that got it working.