Automatically adding new row data to formula

I have a few formulas on the top 3 rows of my sheet that are KPIs for the sheet. Since they consume the top few rows, I cannot select the entire column in the formula or it returns an error of Circular or Blocked. So I need to start the range with (ColumnHeading4:ColumnHeading99) and I cannot figure out how to always include the last row of data. The sheet is our project's issues log and new issues continue to be added via a form to the first empty row at the bottom of the sheet.

Right now I have to change the formulas on a regular basis so that I always include all of the rows in the data.


Is there a way to signify in the formula that it should always include the last row that contains data?

Thanks - David

Tags:

Best Answers

  • Jennifer Greenough
    Jennifer Greenough Overachievers Alumni
    Answer ✓

    Hello,

    We have gotten around this in a couple of different ways. Options we have used are:

    1. Use sheet summary rather than putting the formulas at the top of the sheet.
      1. Pros: Cleaner than putting them on top, less likely a sheet user will delete or modify them, can refer to the entire column, possible to run a report for a dashboard, can be replicated across multiple sheets easily (and then one can run a report across all those sheets), cannot link from/to another sheet (we have run into this when using Control Center)
      2. Cons: Formulas not easily viewed a the top of the sheet (if that is desired).
    2. Place the formulas in a column not used in the formula and then reference the target column using the ColumnHeadingX:ColumnHeadingX method. When we do this, we include a header row called "Metrics" and use one column as the descriptor and a different one as our formula column (see example - these metrics are linked out to other sheets, as you can see from the blue carrots/arrows).
      1. Pros: Visible at the top of the sheet, can be more intuitive than sheet summary, can link these to another sheet.
      2. Cons: May require adding extra columns to the sheet, users may inadvertently delete the metrics (if many people have access to the sheet), more difficult to run reports than sheet summary.


    Another way is to add new rows to the middle of a sheet, but this only works if you are manually adding rows, since automations will allow you to choose either the bottom or top of the sheet.

    Ping me if you have more questions & curious to hear of other solutions.

    Jennifer

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

    @David31406

    Excellent!

    You're more than welcome!

    Remember! 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!

    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å ✭✭✭✭✭✭

    Hi @David31406

    I hope you're well and safe!

    Have you looked at moving the metrics to the Sheet Summary section instead?

    Would 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!

    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.

  • Jennifer Greenough
    Jennifer Greenough Overachievers Alumni
    Answer ✓

    Hello,

    We have gotten around this in a couple of different ways. Options we have used are:

    1. Use sheet summary rather than putting the formulas at the top of the sheet.
      1. Pros: Cleaner than putting them on top, less likely a sheet user will delete or modify them, can refer to the entire column, possible to run a report for a dashboard, can be replicated across multiple sheets easily (and then one can run a report across all those sheets), cannot link from/to another sheet (we have run into this when using Control Center)
      2. Cons: Formulas not easily viewed a the top of the sheet (if that is desired).
    2. Place the formulas in a column not used in the formula and then reference the target column using the ColumnHeadingX:ColumnHeadingX method. When we do this, we include a header row called "Metrics" and use one column as the descriptor and a different one as our formula column (see example - these metrics are linked out to other sheets, as you can see from the blue carrots/arrows).
      1. Pros: Visible at the top of the sheet, can be more intuitive than sheet summary, can link these to another sheet.
      2. Cons: May require adding extra columns to the sheet, users may inadvertently delete the metrics (if many people have access to the sheet), more difficult to run reports than sheet summary.


    Another way is to add new rows to the middle of a sheet, but this only works if you are manually adding rows, since automations will allow you to choose either the bottom or top of the sheet.

    Ping me if you have more questions & curious to hear of other solutions.

    Jennifer

  • I had completely forgotten about Sheet Summary. Thank you both for the reminder and great direction to solve my formula issues. I really appreciate your rapid responses.

    @Jennifer G. @Andrée Starå

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

    @David31406

    Excellent!

    You're more than welcome!

    Remember! 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!

    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!