Complex formulas ammendment
Hi All,
I have an existing Smartsheet that has some relatively complex formulas to count the the status of an action as there are several conditions for an action to show as "Open" and "Overdue". Over the last few days, we have modified the sheet further by adding columns and each added column now comes under as added criterias for "Open" and "Overdue" actions which means that I need to modify my formulas further so that I capture the aditional criterias as well. It's a slightly complicated sheet so I will try my best to explain what I am exactly after so please bear with me.
1) How this sheet works is that some members of our team use a form linked to the sheet to fill in their requirements and once there is an entry in the sheet, there is a series of stages the query needs to go through, before it is approved. These queries need to usually go through 4 departments before it's classified as approved. I have attached below a flowchart of the process of this sheet for your understanding.
2) I have taken an export to excel and attached a copy of the layout of the Smartsheet for your understanding. I wasn't able to capture a screenshot of all the columns in the Smartsheet in one screen grab so I thought this was the best way to do it. It is the columns highlighted in yellow ("Open", "Overdue" and "Post AFC Change Status" columns) that have underlying formulas which now needs modifying to capture the requirements from the added columns (highlighted in green).
3) I have attached a screengrab of the underlying formulas currently in the "Open", "Overdue" and "Post AFC Change Status" column that needs modifying. Can the formulas be modified in the following way:
- Post AFC Change Status: Add dropdown criteria no. 5 and 7 in the formula higlighted in green in the excel spreadsheet
- Open: Add criteria of "Post-AFC Change Status" is "DPE Rejected" and "PM Rejected.(image of already existing formula below)
- Overdue: Add same criteria above (image of already existing formula below)
Please ask if you require further clarity.
Thanks,
Sahil
Best Answers
-
Post AFC Change Status:
We can put in the additional statuses, but we would need the criteria for what would trigger them.
Open:
You would add your additional criteria into the OR section.
=IF(OR([Post AFC Change Status]@row = "5. Rejected by PE", [Post AFC Change Status]@row = "7. Rejected by DPE", ...........................................................................)), 0, 1)
Overdue:
You would do the same as for Open. Add the new criteria to the OR statement.
-
Then you would just add those two additional IF statements to your existing.
=IF([DPE Rejected]@row = 1, "8. DPE Rejected", IF([PE Rejected]@row = 1, "9. PE Rejected", IF([LDO QC Rejected]@row = 1, ...........................................))
Answers
-
Post AFC Change Status:
We can put in the additional statuses, but we would need the criteria for what would trigger them.
Open:
You would add your additional criteria into the OR section.
=IF(OR([Post AFC Change Status]@row = "5. Rejected by PE", [Post AFC Change Status]@row = "7. Rejected by DPE", ...........................................................................)), 0, 1)
Overdue:
You would do the same as for Open. Add the new criteria to the OR statement.
-
Hi Paul!
Thanks for your help. The "Open" and "Overdue" formula is now sorted. The criteria for the post afc change status formula are as follows:
- If DPE Rejected box is checked then post afc change status should change to 8. DPE rejected.
- If PE rejected box is checked then post afc change status should change to 9. PE Rejected
-
Nevermind, I have managed to figure out the formula. Thanks for your help, Paul. I will mark this query as complete and answered.
-
Then you would just add those two additional IF statements to your existing.
=IF([DPE Rejected]@row = 1, "8. DPE Rejected", IF([PE Rejected]@row = 1, "9. PE Rejected", IF([LDO QC Rejected]@row = 1, ...........................................))
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!