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.

PAC process (1) (1).pdf

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).

Post AFC Change Register (1).xlsx

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)

Open.PNG

Overdue.PNG

Post AFC Change Status formula.PNG

Please ask if you require further clarity.

Thanks,

Sahil

Paul Newcome

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.

Paul Newcome

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, ...........................................))

Paul Newcome

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.

sahilhq

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

criteria screenshot.PNG

sahilhq

Nevermind, I have managed to figure out the formula. Thanks for your help, Paul. I will mark this query as complete and answered.

Paul Newcome

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, ...........................................))

Paul Newcome

Happy to help. 👍️