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