How do I formulate a formula that will correspond always to the last form submission (line)?

Andi Reisner

Hi Paul,

I have just tried to run a webform to insert new data in the page and unfortunately the display remains on the old last insertion.

Hence, Joav Banet has issued his last webform into line 2063 but the previous data from line 2058 still appear.

Any idea how can this be corrected, so that the formulated cell (line 2) will reflect the result from line 2063 - All this under column AED Check (Monthly)?

Paul Newcome

Did you update all of the row references in the formula to reference row 7 instead of row 3?

Andi Reisner

Hi Paul,

Yes I have.

It looks like that:

=INDEX(DESCENDANTS([AED Check (Monthly)]7), MAX(COLLECT(DESCENDANTS([Row #]7), DESCENDANTS(Name7), "Joav Banet", DESCENDANTS([AED Check (Monthly)]7), @cell <> "")) - 7)

Paul Newcome

Ok. I want to try something...

Remove the COUNTIFS for the [Row #] from the top section and put it in row 8 with the following adjustment:

=COUNTIFS(Date$8:Date@row, OR(@cell ="", @cell <> ""))

This will essentially "renumber" your rows so that row 8 shows "1" and gives the number for the row within the actual range we are pulling.

Then remove the "- 7" from the INDEX formula.

=INDEX(DESCENDANTS([AED Check (Monthly)]7), MAX(COLLECT(DESCENDANTS([Row #]7), DESCENDANTS(Name7), "Joav Banet", DESCENDANTS([AED Check (Monthly)]7), @cell <> "")))

Andi Reisner

Thanks Paul,

So I have inserted the new COUNTIFS in row 8 and copy pasted the INDEX formula under row 2 that corresponds to "Joav Banet".

Unfortunately it did not grab the last insert from Joav (row - 2053, marked in green)

Anything wrong I have done?

Paul Newcome

Ok. What number does this now produce?

=MAX(COLLECT(DESCENDANTS([Row #]7), DESCENDANTS(Name7), "Joav Banet", DESCENDANTS([AED Check (Monthly)]7), @cell <> ""))

Andi Reisner

Hi Paul,

2051... See below

Paul Newcome

Delete the name you want to pull and then re-enter it. It's like it isn't registering that the name matches.

Andi Reisner

Thanks Paul,

I have played with it a little and here are my findings.

Inserting a name (under "Name") manually in the page generates automatically a followup number in our "Row #" column which brings the correct display of the last insert in our formulated cell. So, all good from this perspective. However, not ideal as I need to insert the data manually. Whereas my colleagues need to feed the sheet with a webform.

But...

When I issue the webform from some reason "Row #" does not update itself to the following number and even if I drag the formula ( =COUNTIFS(Date$8:Date@row, OR(@cell = "", @cell <> "")) ) from row 8 all the way down to the last row, the formulas will not identify that very last row that was generated from the webform.

Below is an illustration where rows 1 and 2 reflect the manual insertion in rows 2066 and 2067. But row 2068 was submitted from the webform.

Any clue how to correct this?

Paul Newcome

Forms won't insert new rows as a child row. That's where the problem lies. You will either need to indent the new rows manually, or you would need to move where the formulas are displaying your counts to a separate column or a sheet summary field.

Andi Reisner

Many thanks Paul, much appreciated!

I will try to find out something.

Best regards,

Andy

Paul Newcome

Happy to help. 👍️