Including hidden form fields with formulas that include row numbers

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

Find more posts tagged with

Forms

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Monthly feedback from multiple individuals
Hello community, I need help figuring out how to set up a sheet to collect monthly comments on several projects. Each project is assigned to a different person who need to provide monthly feedback on its progress. Ideally, I would like them to receive a reminder notification on the report due date, followed by reminders if…
Sheet shows BLANK rows, there is a "MOVE rows" worflow, is that why?
Is it possible that a workflow that MOVES rows to another sheet too basically do a CUT/PASTE (like in Excel), and then you are left with BLANK rows on your original sheet? I cannot think of any other LOGICAL reason why this would happen. The users enter the data from an Intake Form, it gets placed onto this sheet. They do…
Sorting not working?
Hi. I have a sheet where I am sorting dates by ascending order. But the sorting functionality doesn't seem to be working. Instead of bringing all items with date values to the top of the sheeting and sorting them in ascending order, it leaves a gap. Is it a result of the "Next Review Due" column containing a column formula…