Using IF formula to drive status
Mine is a little different than what I've found here. I have 4 columns: Closed Date and Completion Date, Status column that has 4 choices: open, closed, complete, overdue and a Due date column.
If closed and completion are blank, the status should be open
If Due date has passed, status should be overdue.
If completion is populated, status should be complete
If Closed is populated, status should be complete
I have a form attached to this, so when someone completes the form, the sheet populates with that information. When I add these IF formulas, will they be applied to these new rows that are automatically added with the form?
Thank you!
Denise
Comments
-
it worked, however, it marked it complete when it was closed instead of complete. Also, how do I get this formula to apply to rows that are added automatically through a form?
Thank you so much!!!!
-
Sorry about that. I was going by the list in the original post. Try this one instead...
=IF(ISDATE([Closed Date]@row), "closed", IF(ISDATE([Completion Date]@row), "complete", IF([Due Date]@row < TODAY(), "overdue", "open")))
.
As long as the formula is in two consecutive rows, it will auto-populate as new rows are added.
-
This worked perfectly! THANK YOU. I guess this is where algebra would have paid off? I have one more I can't figure out.
=IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green")))
This formula dictates the RGYB and works. I'd like the ball to turn blue if the [closed date] or the [completion date] cell is filled. I'm not doing something right!
Can you help? Thank you!
-
If a row is added automatically through a form, what would the formula be for a dropdown cell [category] to fill in "form". I want to indicate the source of the incoming question as we use an email alias as well. Thanks! again!
-
Hi Denise,
If I understand your question correctly, I'd recommend adding the drop-down value "Form" as a default value in the form and hide it.
Would that work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you. This worked beautifully. I have one more! Sorry, I think I used that "one more" last time, however, in the above thread, I was given a formula to manipulate the open, closed, completed, etc. the formula works great. I would like to have an override in there. So that IF the [completion date] cell has a value, it will override the status and make it "complete" vs "closed." Closed is a bit of a first step. it means the answer has been provided to our submitter. However, IF a question is added to our document, that would then make it "complete." Here is the formula I have now - not sure what/where to add! =IF(ISDATE([Closed Date]@row), "closed", IF(ISDATE([Completion Date]@row), "complete", IF([Due Date]@row < TODAY(), "overdue", "open")))
-
It is simply a matter of rearranging the order of the IF statements...
Current:
=IF(ISDATE([Closed Date]@row), "closed", IF(ISDATE([Completion Date]@row), "complete", IF([Due Date]@row < TODAY(), "overdue", "open")))
.
Adjusted:
=IF(ISDATE([Completion Date]@row), "complete", IF(ISDATE([Closed Date]@row), "closed", IF([Due Date]@row < TODAY(), "overdue", "open")))
-
Thank you again. Have to say this is not one of, but THE best support community I've ever engaged with. Thank you for the swift, accurate and patient answers from someone that is a "letters" person vs. a "numbers" person. These formulas make me crazy. Thank you for making it easy.
Denise
-
Help Article Resources
Categories
Check out the Formula Handbook template!
