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

Hi There,

I have been trying to formulate a cell which will correspond to two columns.

In Column "Name" I would like to take "Bernhard Schneider" the last submission (in this case it is line 2051) and place his yellow exclamation mark, or red x, or green tick in line 1.

Note, Bernhard Schneider will continue to submit his webform regularly and therefore I need the display to correspond always to his last submission, hence, not necessarily to only line 2051, but also future lines which will be submitted, but always his last corresponding line.

And thereafter, in line 2 under column "AED Check (Monthly)" to display the last submission from the next name, in this case "Joav Banet". Also here on a continuous formulated formula as above.

Is there a possibility to do that?

I will be grateful for any kind of support.

Kind regards,

Andy

Find more posts tagged with

Accepted answers

Paul Newcome

Hmm...

=INDEX(DESCENDANTS([AED Check (Monthly)]3), MAX(COLLECT(DESCENDANTS([Row #]3), DESCENDANTS(Name3), "Bernhard Schneider", DESCENDANTS([AED Check (Monthly)]3), @cell <> "")) - 3)

Try plugging this in... the MAX function provides the row number, but the problem is, there may not necessarily be that many rows within the DESCENDANTS of [AED Check (Monthly)]3. Since we are starting our range three rows down, then maybe we need to subtract 3 from the row number using the above.

How does that work?

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.