# Forms & Functions

I made a time sheet with a Form. When i enter the number of hours in the form and the number entry is entered into the sheet from the form it breaks the SUM function in the sheet.

Any solutions

• ✭✭✭✭
edited 03/03/20

Yes, in order for the "auto-fill" feature to work on a new row, the cell being filled must be empty.

My solution was to make a "helper column" for the formula to live in, then make column for hours that corresponds to a field on the form. (The helper column doesn't have a corresponding field on the form, so it never breaks.)

Also, I have all my new rows come in at the top of the sheet, so I never end up with auto-generated, ten-row breaks at the bottom of the sheet between the previous last row and new rows. This quirk of Smartsheet used to break all my formulas; however, since switching to having them come in at the top of the sheet, I've had no issues.

• ✭✭✭✭✭✭

What do you mean by it "breaks" it? Does it go from calculating to an error, incorrect range, incorrect calculation, other?

• The sheet gets filled at the top row (Row #1). The last column labeled (hours) in row one contains a sum function in it. Row 30 in the column labeled (hours) is suppose to total rows 1 thru 29. Wen hotrs colume row 1 has the information placed in it from the form the sum function is erased.

• The sheet gets filled at the top row (Row #1). The last column labeled (hours) in row one contains a sum function in it. Row 30 in the column labeled (hours) is suppose to total rows 1 thru 29. When the hours column in row 1 has the information placed in it from the form the sum function is erased in row 1 so row 30 cannot total anything.

• ✭✭✭✭✭✭

@Garry Lind In that case I would suggest a solution similar to what @Colin Entrekin suggested.

For the row based SUM calculation, you will need to create a column not associated with the form to run this calculation. This way there is no data overwriting it when a new form is submitted.

For the issue with Row 30, you are going to want to reference the entire column. That way the range doesn't change when you add in a new form (e.g. [Sum Column]1:[Sum Column]29 becomes [Sum Column]2:[Sum Column]30 when a new row is added to the top).

A recent discovery (I am not sure when it was enabled, but it was only recently noticed) allows you to put a formula in a cell that is within the range that you are pointing at in the formula without throwing a #CIRCULAR REFERENCE error. Granted there are rules and limits to it, but for this use case, it should work just fine.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!