Automatically add formula to new rows

Nataleigh
Nataleigh
edited 12/09/19 in Formulas and Functions

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!

Tags:

Answers

  • Andrée Starå
    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

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I am having the same issue

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • MCorbin
    MCorbin Overachievers Alumni

    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
    MCorbin Overachievers Alumni

    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å
    Andrée Starå ✭✭✭✭✭✭

    @MCorbin

    Glad you got it working!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PGilbert
    PGilbert ✭✭✭
    edited 04/04/24

    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!

  • Andrée Starå
    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: 

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PGilbert
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!