How do I make formulas automatically carry down to the next line when a new form is submitted?

TJones Hoshizaki

I have a sheet that is being fed by a form. I am using a vlookup to add contact data from another sheet dependent on the email of the person who submitted the form.

I want those vlookups to be added automatically to each line as new forms are submitted.

How do I do this?

Paul Newcome

As long as there are two rows containing the formula(s) above and/or below the newly submitted row, the formulas should auto-fill into the new row.

TJones Hoshizaki

@Paul Newcome

That is what I read but that is not what is happening. I have almost 80 lines above that all have the formulas yet when a new form is submitted it is not bringing the formulas down.

Is there a bug that needs addressed?

Paul Newcome

Are the rows above the new form entry of the same hierarchy as well? Are there any blank rows immediately above the new row?

TJones Hoshizaki

@Paul Newcome

Yes they are all the same hierarchy. There are no parents or children in the list. Everything is on the same level.

When I put in a test line in the form it lands right below the previous line but the formulas do not drop down.

TJones Hoshizaki

@Paul Newcome

I just did another test entry and this time it did work. I will keep an eye on it and report back if I see any inconsistencies.

Jwindels

@Paul Newcome

On that same topic, I have a Vlookup formula that I was able to get working and populates the fields pulling the desired information from another sheet when a particular option is chosen on the submission form. However, when the other option is chosen for that field on the form the data in those cells is overridden and Vlookup stops working for future entries.

Example of when it works:

=IF([Request Type]14 = "Prior Resource - See Outside Resources Sheet", (VLOOKUP([Full Name]14, {Resources Database (Demo) Range 1}, 9, false)))

Once form submission "[Request Type] = New Resource" the user will enter the data for the fields that the Vlookup is in for Prior Resource selection.

Then next entry will not use Vlookup formula. How do I get the entire sheet to use formula regardless of last entry?

Andrée Starå

Hi @Jwindels

You'd need to move the row before changing it, so there always are at least two rows before/after with the same formulas and structure.

Make sense?

Would that work/help?

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å

@Jwindels

To add.

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