Formula to include new data added to my sheet

Irene Plant

I have set up a formula that works very well however it isn't taking into account new entries added to my sheet. For example my formula is on Row 1 under the column 'total' I then have other information in rows 2-15 so therefore my formula on Row 1 starts from row 16 to give me the total I want into Row 1. My formula in row 1 is =SUM(total16:total18) this works fine for the data I have at the moment because I only have three lines of data (rows 16-18) however when I add in data to row 19 it obviously isn't included in the total in row 1. It wont allow me to make the formula open ended so that any new data is included in the formula. Any thoughts?

Many thanks, Irene

Richard Rymill SBP

Hi Irene

If possible the simplest solution is to use Hierarchy then add all the New (and old ) children can add up to the top. =SUM(CHILDREN()). 

 

That is the simplest solution, there are others... 

 

Hope that helps?

 

RichardR

Irene Plant

Hi Richard,

Thank you for this suggestion but can't really get it to work as there are other rows with data in the way which already have indents (rows 2-15)

JohnHinkle

You could have multiple sets of hierarchy in your sheet. If 2-15 is one set, you can start a new set of hierarchies on row 16. 

 

There are two ways to make an "open ended" formula range. The first is to use what Richard hierarchy - indent the rows under a parent and use CHILDREN(). The other is to reference the entire column, like this:

 

=SUM(total:total)

 

It sounds like you have other data in this column, so it might not work for you. You could create a new column that only contains the data you are adding together then use the column range. 

Irene Plant

Hi,

Thank you for your response I have tried to use the hierarchy by indenting the rows beneath and use the CHILDREN() formula, the only problem is the webform entries do not automatically indent when being entered so everytime there is a new web form entry you will have to manually indent that data?

 

KrisWalsh

My work-a-round was to change the "Location of New Entries" to "Top of the Sheet" in Webform Options.

 

When I open the sheet, all the new entries are at the top. I select all of those rows and drag them under the header.  They automatically indent when I do this.