My form submissions won't pull the formulas from the sheet.

Allie Forbes · 09/19/20

Hi, I have a sheet that I am using to manage goal/task tracking for my team. I have some formulas to calculate %completion based on how many boxes are checked in a row and a join formula for another purpose. I am trying to break this sheet down by project and am using parent/child rows to achieve this. The first row is a parent row and I don't want it to include formulas because that is messing with the conditional formatting I had set up to display the parent rows with their own format. Because of this, I added a dummy row above it that included all the formulas that each new form submission will need (new submissions are sent to the top of the sheet). However, the new submissions still aren't pulling the formulas.

I've pasted a screenshot of my sheet below. Row 2 is the dummy row with the formulas and row 1 is the most recent form submission which is not pulling the formulas. The row with the green box saying "On Track" is how I would expect the first row to look after checking the Step 1 box.

Does anyone have any ideas on how to make this work? I'd also love to hear any creative solutions to place the new rows automatically below their designated parent row.

Andrée Starå · 09/19/20

Hi @Allie Forbes

You'll have to add another row at the top (with the same formulas and hierarchy) for the auto-fill to work.

The new row will be auto-filled with the formulas if there are two rows below/above with the same formulas and the same level of hierarchy.

More details:

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

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 topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
At the bottommost of the sheet if it’s below 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.

More info:

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

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå · 09/19/20