How can I fix my sum formula ranged whenever I have new inputs from Forms?

Benjo090

Hi, I have sheets where I am collecting the data using the form. I have setup my form to put new submitted data at the top of my table. My problem is , when new submitted data was collected the range of my formula is being changed and not getting the new collected data. See the Total formula.

Please help me to fix the range of my SUM formula or there is another way to do it?

Casey DuBose (Jalubro Consulting)

The only way to prevent a manual intervention of some sort is to create a separate sheet where you display the totals from you lead tracker. You can use a cross-sheet reference to sum the total column and it will always be updated.

Benjo090

That's why I thought. Then I will just linked the cell to my sheet. Thank you Casey.

Benjo090

Hi @Paul Newcome, do you have a way around?

Paul Newcome

You can have one single "circular reference" in a column. So if the only formula that is going to be in that particular column that also references that column is your SUM, then you could use

=SUM([Potential GWP (OMR)]:[Potential GWP (OMR)])

in the [Potential GWP (OMR)] column without creating a circular reference.

Another option could possibly be to use a Sheet Summary field or as previously suggested a separate metrics sheet.