Automatically adding new row data to formula

David31406

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

Jennifer Greenough

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å

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

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!

Jennifer Greenough

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

David31406

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å

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

jeditig

Yes, we moved all our headers into the sheet summary and then can pull the info from there. Saved us all those headaches.