Blank Rows in Form Submission - Deleting rows ends by column calcs

Options

Hello - when I have a new form submission it seems to come in at the bottom of my column calcs. For instance I have a calc set up to row 375 and the submission will come in at row 376 and wont be included in my calcs. If I delete all the rows in between like several posts have suggested that ends my calcs at the line above the one I just moved. I have been cutting and pasting rows as these come in, but this is not sustainable. Any suggestion on how to get rid of the empty rows and still keep my full column calcs?

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Volson,

    What is the formula you're using? Is it only included on one row before the form?

    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!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @Volson

    Hi Valerie,

    I'd recommend a different structure, if possible.

    Regarding the empty rows

    Try deleting everything from row 49 and save the sheet, and then the submissions should come in at the last empty row.

    Regarding the metrics

    Either I would,

    • add all the metrics to the Sheet Summary section 
    • or to a different section in the sheet and not in the same columns
    • Or create a so-called Master Metric Sheet where you'd collect all the metrics
    • If you want them as it's structured now, we could add a so-called helper column to make it work with minimal effort.

    The best method depends on your structure in general.

    What do you think? What would work best?

    Be safe and have a fantastic week!

    Best,

    Andrée

    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.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Volson,

    What is the formula you're using? Is it only included on one row before the form?

    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!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you have two rows above that have the formulas in them, then auto-fill should pull those formulas down as rows are added. You shouldn't need to pre-fill rows.

    Or are your 375 rows being used for something else?

  • Valerie Olson
    Options

    Thanks Paul, but when the rows are added they are added to the bottom of the form so they aren't autofilling for some reason. Then when I cut and paste them above the formula no longer autofills. Am I missing something?


    Andree - I have the top 5 rows of the form set aside to calc some metrics I want visible to my team as the submissions come in so they can see the real-time stats. Each of these rows are a different metric. Then the form submissions start. I am sharing to you now. I recreated a sheet and will share with you now. Thank you!

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 04/07/20
    Options

    Just a suggestion but why don't you move your column calculations to a different sheet and just have the form updating one sheet and your statistics being processed on another. Then the new entries won't mess up your stats.

    OR in your form design screen you can change the setting to have new form entries appearing at the Top of the sheet.

    Personally I would move the column calulations to a new sheet.

    Hope this gives you an alternative view on how to resolve...

    Kind regards

    Debbie

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @Volson

    Hi Valerie,

    I'd recommend a different structure, if possible.

    Regarding the empty rows

    Try deleting everything from row 49 and save the sheet, and then the submissions should come in at the last empty row.

    Regarding the metrics

    Either I would,

    • add all the metrics to the Sheet Summary section 
    • or to a different section in the sheet and not in the same columns
    • Or create a so-called Master Metric Sheet where you'd collect all the metrics
    • If you want them as it's structured now, we could add a so-called helper column to make it work with minimal effort.

    The best method depends on your structure in general.

    What do you think? What would work best?

    Be safe and have a fantastic week!

    Best,

    Andrée

    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.

  • Valerie Olson
    Options

    All very helpful comments. Thank you to everyone. I have decided to move everything to a separate metric summary sheet. I just have quite a few to go back and move! :) I know for moving foward though.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Volson Good decision. I tend to use this method to keep my source data and my stats for dashboards separate. Good luck in shaping your sheets.

    Debbie

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Volson

    Excellent!

    Happy to help!

    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!