Modifying rows in a form entry sheet with formulas

Corentin Dal Farra

Hello everyone,

I have a problem when I change some date in rows in my sheet, here is the example :

• A user fill information in a form, and then a new row is added in the sheet related to the form
• On that sheet, there are a lot columns that have pre-defined formulas
• I have put the new form entries parameter on the top of my sheet so each time a new row is added, it is added on the top.

As I understood, If I change the data in a formula column in the most recent row (on the top), it will affect all the new rows entries in the future, but if I change the data in a formula column in any other row it will not affect all the new rows .

Do you know how I could prevent this from happening and keep my formulas "freeze" whatever happens ?

I was thinking about column formula new feature, but I want to be able to change formula in a row after submission..

Thank you very much for your help !

Corentin

Andrée Starå

Hi @Corentin Dal Farra

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

Also, if possible, I'd recommend using the new Column Formula because then it will always work.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Did that work/help?

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!

Andrée Starå

Hi @Corentin Dal Farra

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

Also, if possible, I'd recommend using the new Column Formula because then it will always work.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Did that work/help?

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!