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

Andi Reisner

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

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?

Paul Newcome

Is it just going to be those two names?

Andi Reisner

Hi Paul,

No, there will be six of them in total. But I am planning to place for each of them his/her own line with the formula.

Paul Newcome

I am going to suggest inserting a text/number column called [Row #]. Then enter this formula on row 1 and dragfill down the remaining rows.

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

Then insert a row above Row 3 and indent everything below that.

Once you have done that, you can use something along the lines of...

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

Andi Reisner

Dear Paul,

Many thanks for your reply and apologies for my slight late response to it.

Sorry but in which cell would I have to insert the formula you have advised?

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

Anywhere I insert it I get an #INVALID VALUE.

My apologies for this headache.

Paul Newcome

The INDEX formula would go into the cell where you want to populate the data that is pulled. So n your screenshot above, it would go there in row 1.

Andi Reisner

Thanks Paul,

So if I understood correctly I have inserted the INDEX formula and still get the #Invalid (Line 1 / AED Check Monthly).

Anything I did wrong?

See below the updated screenshot based on your guidance:

Paul Newcome

Check your sheet to see if that error is present anywhere else.

Andi Reisner

Hi Paul,

Yes, that error #INVALID appears all over each cell I insert it in.

Any ideas?

Paul Newcome

I don't mean insert the formula into other cells. I mean take a look at the data that is already present. If that error is present in any data that the formula is referencing, then the error will be pulled through the formula itself. I want to check to see if that error is present elsewhere to determine whether it is a problem with the formula or the data.

Andi Reisner

Sorry Paul I am a little lost.

I do not see any problem with the data other than might be something with the formula, but I might be wrong.

Paul Newcome

Make sure that COUNTIFS for the [Row #] is in EVERY row.

What happens when you enter this into a cell?

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

Andi Reisner

Thanks Paul,

entering the =MAX formula gives me the number from the ROW# column. Seems we are getting there but not quite. See below:

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?

Andi Reisner

Yeees!!!

That worked, Paul!! You are a star!!!! Many many thanks!!!!

Paul Newcome

Happy to help! 👍️