Forms & Functions

Garry Lind

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

Colin Entrekin

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.

Paul Newcome

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

Garry Lind

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.

Garry Lind

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.

Paul Newcome

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