New data from Web Forms lands outside analysis range

CRL Planning

We have a number of Smartsheets that gather data using web forms.

 

Once the data has been gathered we analyse the responses received via the smartforms using formulea at the botton of the sheet (COUNTIF)

 

This works really well and once we have this data in we can use the charts available in Smartsheet labs to present the data findings in great looking charts.

 

All good so far; but, there is one issue that is really frustrating. When new data is recieved via a webform it populates the top of the smartsheet and falls outside the data range that is being caught by the COUNTIF formula. I have tried all sorts to try and get round this issue but have found any other way other than editing every COUNTIF formula in the sheet and given that there are ove 50 formulae in each sheet this can take a bit of time.

 

Has anyone else come across this issue and if so it would be great if there was an easier way of doing this.

Sam S

There are two options for this. The first is to change your formulas to reference the entire column, rather than a range of cells.

 

=COUNTIF(Status1:Status50, "Red")

 

Turns to:

=COUNTIF(Status:Status, "Red")

 

Remove the row number and the formula checks the entire column, even when new rows are added. Be careful of creating circular references (you cant add this formula to the column it is counting).

 

Another option is to add new responses to the bottom of your sheet, rather than the top (can be sorted later to get the order you want - newest at top). Indent all the rows under a parent row (using hierarchies) then use the COUNTIF(CHILDREN() function which will count all the child rows, even when you add more. The extra step with this, each time a new row is added via a web form, you will need to indent it under the parent.