Formula autofill to the next rows in a sheet when using hierarchies

Megan Wilfong

Has anyone had any success with maneuvering around the fact that new parent rows created by a form and added to the bottom of a sheet will not inherit the formulas of the row above?

Here's the note from the Smartsheet help:

NOTE: Form submissions will always be fully outdented. If the last row in the sheet is indented in hierarchy, a form submission inserted at the bottom of the sheet will not auto fill the formulas or indentation of the previous rows.

I have users submitting change requests via a form and then I go and add the subtasks for that change as children rows under the change request parent. Once I've done that, then a new form submission doesn't pick up the formulas even though all the children of the parent above do. Do I really have to drag the formula manually every time?

Andrée Starå

Hi Megan,

There are two options. I use these all the time when developing Client solutions.

1. Add two rows at the bottom and then move the new submissions
2. Add two rows at the top and then move the new submissions

The extra rows can be hidden with the help of a filter.

Make sense?

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help 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!

Sara_Cook

I have a question that I believe is related to the question above, but the answer of adding rows at the top/bottom won't necessarily work for my situation.

I have a sheet that is used to collect data from a Smartsheet form - the submissions add a row to the top. I have a VLOOKUP formula in one of the columns of this sheet to automatically assign a contact once the form is submitted, and then workflow to alert the contact by email. This works really well *until* the assignee needs to be changed. If someone manually changes the assignee (to someone different than was originally assigned by formula), then the formula breaks and any future form entries don't automatically assign a contact. I will periodically go in the grid and check to see if the formula has been broken and then manually add a row to the top with the active formula so then new submissions catch the formula. But this isn't a sustainable solution. Is there a way to make that column read the formula forever and always even if the row below has a broken formula? Or any other tips on how to make this efficient?

Thanks!

Sara

Andrée Starå

Hi @Sara_Cook

I’d recommend adding two helper rows at the top or at the bottom of the sheet that will be placeholders with the formulas, so all new rows have the formulas auto-filled. Then you can move and change the new rows as needed.

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

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

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!

Sara_Cook

Hi @Andrée Starå

Thanks for that info! I have tried adding two helper rows at the top, but the problem is that when a new form submission comes in, it populates to the top (we want the rows to be most recent at the top), and then if someone updates the Assigned To column in that newly populated row, now the top row formula is broken. I've tried to see if I can lock the top two helper rows into place at the top, but I can't. And manually updating the Assigned To column always breaks the formula.

Thanks,

Sara

Andrée Starå

@Sara_Cook

Happy to help!

I'd recommend adding them at the top and then moving them to another location on the sheet.

Would that work?