Including hidden form fields with formulas that include row numbers

T Hogan

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

Andrée Starå

Hi Tom,

If I understand you correctly, you wouldn't need the formula to accomplish what you want.

It could set it up with Alerts & Actions.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

T Hogan

Thanks so much for taking a look at this. I've shared a copy of the Smartsheet with you. The Request Status Column is the one that contains a formula based on the adjacent three approval columns.

A user submits the form requesting a UAS mission and it has to be approved by the UAS Coordinator, UAS Aircraft Reporting Custodian and NSA-A in order for the Request Status to be changed to Approved. Currently, we are sending an approval request to all three approvers at once (currently the same people since we're testing this out). Currently, the Request Status field is blank when a new request is submitted, but I'd like to find a way to have it insert the formula below, using the actual row number rather than always referencing the first row.

=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"))

If you have any further questions, please don't hesitate to contact me.

Sincerely,

Tom

Andrée Starå

Hi Tom,

I'll add the answer here as well for others convenience.

I've added @row in your formula, so no matter what row it's on it will still work.

The formula will get added automatically as long as this is true:

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.

• At the very top of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

Hope that helps!

Best,

Andrée

T Hogan

Thank you. I added a hidden field in the form and put the formula in as the default value.

 

 

That populated the field in the Smartsheet when the form was submitted, but it shows up as the formula rather than the results of the formula.

 

 

 

That appears to be because they've actually added an apostrophe at the beginning of the formula which makes it treat it like text rather than a formula. Removing the apostrophe activates the formula, but that's really not much better than requiring my client to use fill-drag to extend the formula to the new cell. Is there a way to avoid having Smartsheet put in that apostrophe?

 

 

 

Sincerely,

 

Tom Hogan

Andrée Starå

You're welcome!

You don't need to add that column to the form. It will auto fill the formula anyway.

Let's continue the discussion thru email.

Best,

Andrée

T Hogan

Thanks...guess I didn't need to worry about the row issue in the formula at all. Perhaps it wasn't auto-populating initially because I didn't have more than one row with the formula in it. I also had one row that inserted four rows below the rest of them, which was a bit strange. That hasn't occurred again.

I really appreciate all your help and the speed with which you responded! It was a huge help!

Sincerely,

Tom