Updating Status column based on two different columns
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")
Best Answer
-
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))
Answers
-
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 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"))
-
Ah! Perfect. Glad that got it working.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!