How do i get a cell to return a TEXT value based on several IF criteria from other cells?
Hi
I have an audit planner which i am trying to automate some what, i would like the status to change based on a set criteria.
I.e. AUDIT STATUS = "Awaiting Scheduling" IF IMS AUDIT is CHECKED and SCHEDULED DATE is BLANK
AUDIT STATUS = "Scheduled" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPPETED DATE is BLANK
AUDIT STATUS = "Overdue' IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPETED DATE is BLANK and PROPOSED AUDIT DATE is less than TODAY.
AUDIT STATUS = "Completed" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPLETED DATE is NOT BLANK
Any help would be greatly appreciated
Best Answers
-
This ought to do it:
=IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work.
If there are any issues, ensure I got all the column names correct.
=IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row=""),"Awaiting Scheduling",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row<TODAY()),"Overdue",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row<>"", "Completed",""))))
Answers
-
This ought to do it:
=IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work.
If there are any issues, ensure I got all the column names correct.
=IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row=""),"Awaiting Scheduling",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row<TODAY()),"Overdue",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row<>"", "Completed",""))))
-
Great, many thanks guys, most appreciated.😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!