How do I count new rows from web forms?

Colin Entrekin

Hello,

 

I'm trying to count new rows that come in on a web form below a specific row using a formula. How do I start my formula range on a specific row, then count all new rows that are below it as they come in from the webform?

 

We use a webform to receive quotes from subcontractors.  As they come in, they are moved up to be filed under the company "parent" row as children.  Each time this happens, the formula errors if I am using the column range formula without a row number; however, if I put in the row number, I cannot count new rows.  How do I fix this?

 

Ex.

=COUNT([Quote #]:[Quote #])

 

- This counts all rows, but includes old quotes.  Not what I want.

 

I want to start at a given row, then allow it to change as we move numbers from below it to above it, but also add new rows that come in from the form...

 

Ex.

=COUNT([Quote #]$250:[Quote #])

 

As you know, that formula doesn't work, but hopefully you get the idea, and see what I'm trying to do..

 

J. Craig Williams

Colin,

 

If you know the row where the new/old split starts, you could simply change your formula to

 

=COUNT([Quote #]:[Quote #]) - 249

 

You might run into problems when moving the rows from new to old using another formula.

 

This post (by me) might help. I'd need to give it more thought to be sure.

 

http://ronin-global.com/2017/01/09/smartsheet-referencing-a-cell-that-doesnt-exist-yet/

 

Hope that helps.

Craig

Colin Entrekin

It's a good idea, but I'm having two problems with it:

 

1) The formula is returning the wrong value.  For example, I only have two new invoices; however, the formula is returning "16" as the answer, when it obviously should be "2".  Even if I add in the ten blank rows that Smartsheet adds by default below the last row, the answer would only be "12".  Not sure how it's getting "16".

 

2) As I move new rows up (above the split), the number of the last row will necessarily increase (Ex. "249" will become "250, 251, 252," and so on.) How do I address that?

 

Thanks for your help!!!

J. Craig Williams

Colin,

 

Without seeing the sheet, I can't see a reason for 16 instead of 2.

 

The formula above (with a hard-code 249) requires manual update.

Depending on the way the rest of the sheet is structured - for example, is there a column that is filled in and which tells someone else that the row can be moved? - there might be a way to determine what the number of 'processed' rows (for lack of a better term)

 

Craig

Colin Entrekin

Craig, 

 

Thanks for the help.  I actually did a workaround using your idea, after playing with it to get the right answer each time. 

Basically, I just have one cell calculating the total rows in the column, and another cell calculating the rows after the split to the bottom of the sheet.  Then, a final cell calculating the difference between those two which is the total new rows.

Total rows accounts for all new rows on the web form, and "Processed Rows" (your term) accounts for all the rows previously filed.  Seems to work.  Thanks!

 

(Note: I had to change the webform so that new rows come into the top of the sheet, rather than the bottom.  It seems to work better that way for this method.)

 

Here's the formulas...

 

Total Rows Cell:

=COUNT([Quote #]:[Quote #])  

 

Processed Rows Cell:

=COUNT([Quote #]2:[Quote #])250

 

(Note: "250" is currently the last row on my sheet. New rows will come in on row "1".  Thus, everything between 2-250 will be processed rows.  Also, the numbers will change automatically as new rows come in, or I move rows to "processed.")

 

 Total New Rows Cell:

=SUM([Total Rows Cell] - [Process Rows Cell])