Formulas Not Autofilling With Form Submission

Joe Liotta

I'm having some issues with a formula that refuses to autofill with a form submission. The form is submitting new entries at the top of the sheet and I have two cells below it that contain the formula. According to this help article, it should be autofilling the formula into the new row.

What has been driving me crazy was that it worked several times before and it decided to stop working after I added some automated workflows to the sheet. I tried disabling the workflows but I still can't get it to function properly.

I'm a bit new to using Smartsheets so I have never run across this issue before. Does anyone know what might be causing my problem? Also, if you need any clarification or additional info, please let me know.

Joe Liotta

I talked with Smartsheet Support. They told me that there is a known bug that might be causing the problem:

"Please also note that there is a known bug when a form is submitted, formulas won't auto-fill if they reference a field in Sheet Summary."

The formula that's giving me issues is referencing the sheet summary so that's most likely the problem. Just letting everyone know for future reference if anyone runs into the same issue and comes across this thread.

Andrée Starå

Hi Joe,

I'd be happy to take a quick look and see if I can spot the reason for it not working.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Joe Liotta

Hi Andrée,

The sheet that I'm making is for our field engineers to track their sales opportunities. The form is used to populate the "Opportunity Name", "Owner", "Activity", "Other Description", "Customer Name", "Market", "Customer Address", "Start Date" and "Expected Closure Date" fields to create a new opportunity in each row of the sheet ("Opportunity Name", "Other Description", "Market", "Start Date" and "Expected Closure Date" fields are hidden in the actual sheet and all above fields are locked). The formula in question (see below) combines the info in "Market", "WHOTO" (in the Summary Sheet), "Opportunity Name" and "Other Description" to create a "Project Name".

=IF(ISBLANK([Other Descriptions (Optional)]@row), Market@row + "-" + WHOTO# + "-" + [Opportunity Name]@row, Market@row + "-" + WHOTO# + "-" + [Opportunity Name]@row + "-" + [Other Descriptions (Optional)]@row)

After creating an opportunity, the engineer has 5 fields that they can update: "Status" of the opportunity, "Estimated Total Sales Amount", "Actual Total Sales Amount", "Service Charge Amount", and "Product Pull-Through Amount" (i.e. product sales from the field engineer's service). Additionally, if the "Status" is not set to closed, an update request will be sent to the opportunity owner each month to fill in these fields and remind them to close the opportunity. When "Status" is changed to closed, the "Closure Date" auto-populates from the below formula (this one does autofill with each new form submitted) and the row becomes locked.

=IF(Status@row = "5. Close", [Modified Date]@row)

Also, I'm happy to share my sheet. It is still in the development phase and only contains dummy data so there is no sensitive info in it.

Andrée Starå

Thanks Joe,

I'll take a look and get back to you!

Joe Liotta

I talked with Smartsheet Support. They told me that there is a known bug that might be causing the problem:

"Please also note that there is a known bug when a form is submitted, formulas won't auto-fill if they reference a field in Sheet Summary."

The formula that's giving me issues is referencing the sheet summary so that's most likely the problem. Just letting everyone know for future reference if anyone runs into the same issue and comes across this thread.

Andrée Starå

@Joe Liotta Thanks for the update!