Complex formulas ammendment

sahilhq ✭✭✭✭✭✭

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.



Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!