Formulas Including New Rows

Hi,

(Sorry if this has already been asked and resolved, I couldn't find anything).

I currently use smartsheet for our team's training log. In doing so, there are several columns that require numerical data that I would like to keep track of.

Right now I use SUM (manually) each time someone fills out a new form, but I wanted to know if there was a way for the cell to automatically include the newly added rows from the forms?

Screenshot Smartsheet.png

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

You'll want to count the entire column range of your column, for example:

=SUM([# Club Staff]:[# Club Staff])

hquinn

Thank you so much for responding Shaine!

When I entered that formula, it's saying "#CIRCULAR REFERENCE" - is there something else I should have done?

Shaine Greenwood

Hi,

That means that you've got this formula in a column that contains another formula that references its data in some way. (Formulas can't reference one another's data in this way or you'll create a circular reference.)

A couple of ways to potentially combat this:

Move the formula to another column.
Reduce the range such that it references cells 1 - x (where x is the bottom of your range).
Create hierarchy in your sheet and use a SUM(CHILDREN()) formula instead. More on hierarchy here: https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows

On #2, you'll need to make sure that you're adding new rows BELOW row 1, somewhere in the range, or you'll run into the same issue with the range not expanding.

A function's range won't expand when you add new rows above or below it.

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…