I'm creating a Smartsheet that has a three-tiered approval process. Requests are submitted via a form, and once the three approving authorities approve the request, I want it to change the overall request status to "Approved." I then use alerts based on that overall request status to send an email out to the requester to let them know their request was approved.
The problem is, since I'm gathering the data via a form, I would have to include the formula in a hidden field or manually enter it in the Smartsheet (via Drag-Fill) after the form is submitted, but I'm not sure if that would trigger my alert if everyone had already approved the request (I'll test that out). If I included it as a hidden field in the form, is there any way to have it adjust the formula automatically to account for the row number of the new cell?
The formula is as follows (basically it results in "Approved" if all approvals are "Approved"; "Declined" if all approvals are "Declined" and "Submitted" otherwise:
=IF(AND([UAS Coordinator Approval]1 = "Approved", [UAS Aircraft Reporting Custodian Approval]1 = "Approved", [NSA-A Approval]1 = "Approved"), "Approved", IF(AND([UAS Coordinator Approval]1 = "Declined", [UAS Aircraft Reporting Custodian Approval]1 = "Declined", [NSA-A Approval]1 = "Declined"), "Declined", "Submitted"))
Sincerely,
Tom Hogan