Automatically add formula to new rows

Nataleigh

I have a sheet with a formula set up to find duplicate email addresses. But, when new rows are added, the formula doesn't copy to the new row. Is there a way to make the the new row also includes the formula. Sidenote: when a new row is created, it also triggers Zapier functions so I can't just drag the formula down into empty rows or it causes a Zapier problem.

Thanks!

Andrée Starå

Hi Nataleigh,

How are the rows added? Top, bottom? Are there at least two rows with the formula before or after?

Can you describe your process in more detail and maybe share 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)

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Nataleigh

The rows are currently added to the bottom. The formula is in at least two rows before the last empty row but not after it for the reasons below. 

We have a Ninja form on our website. When a person completes that form, a Zapier zap is triggered so the information gets added to smartsheet at the bottom. Another zap sends that person a thank you email.

If I copy the formula into what are now "empty" rows, Zapier will think it's new information and look for the email address to send the thank you email which will cause an error.

Because we're watching our number of submissions, we want to make sure there are no duplicates. So, I set up a formula so a box would get checked if a duplicate email is detected. =IF(COUNTIF(Email:Email, Email@row) > 1, 1).

Ella

I too am having the same issue. For the most part I got it to work by incorporating ISBLANK into the formula, however on some sheets I am still having issues with the formula despite that. Still trying to figure it out. 

Andrée Starå

Have you tried/would it work if you add them to the top instead? Maybe it's just a temporary glitch.

Hope that helps!

markrecital

I am having the same issue

Andrée Starå

@markrecital

How are the rows added? At the bottom or the top?

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

MCorbin

Are you guys still having this problem? I've noticed this with just one of my sheets. It's one where I copy and paste (every morning) data from an Excel spreadsheet into this sheet. I have a bunch of calculated fields on the first line of the sheet that used to automatically copy down to the new rows, but for some reason they stopped.

I tried creating a 2nd row at the top to make sure that wasn't the issue, but they're still not copying...

Weirdly, I have another sheet (for another report) where I do the same thing and that one is working fine....

MCorbin

And there I go answering my own question..... I noticed in the sheet that wasn't working, the top row was "locked". I unlocked it and my calculated fields started copying again. Good to know....

Andrée Starå

@MCorbin

Glad you got it working!

PGilbert

I am actually having this same problem except it's code versus a formula. When I add new rows at the top, they do not contain the formula/code that I am using, and I have to copy/paste them into the helper column. Is there a way to automatically include the coding in new rows automatically? I have removed all blank rows, refreshed/saved and am still having this issue.

Code: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

A screen shot is attached. I also tried inserting a row below a row with the code and the code does not show up. Thank you for your help!

Paul Newcome

@PGilbert Have you tried making your formula a column formula?

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

Andrée Starå

Hi @PGilbert

I hope you're well and safe!

Check that the following is true. If possible, you could convert it to a column formula and then it would always auto-populate.

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/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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

PGilbert

Hello! Thank you for these suggestions. I found that by making converting a cell to a column formula, that did the trick just fine (before you kindly offered the suggestion above). I will save this information as well. Thank you so much!

Andrée Starå

@PGilbert

Excellent!

Happy to help!

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