Cell Updates from helper sheet / MAX(COLLECT?

kwieberg

I am trying to update a row based off a helper sheet with form entries. I am trying to take the latest entry for a certain individual and update their status. I am thinking the MAX(COLLECT function is what I am needing however am running into some difficulties.

I have 2 columns column 1 representing the person and column 2 representing their status. I have the same 2 columns in my helper sheet that is being updated with the individual's status from a form. I am trying to get my main sheet to simply update or override the status field with that individual's latest entry (since forms cant update a cell I think this is the easiest method to try).

I have a dashboard based off my main sheet, just needing a way each person can update their status without someone having to make changes on the sheet itself.

kwieberg

I am also using an auto number cell for each row as well as modified date/time to determine the latest entry

Genevieve P.

Hi @kwieberg

I would suggest using a Created Date column instead of a Modified one, to show when the row was inserted into your second sheet. Then yes! You can use a MAX( function to identify the max date for that specific person and bring back data (Status) based on that date.

I would use an INDEX(COLLECT function to bring back the data, with a structure like this:

=INDEX(COLLECT({Column to return}, {Criteria Column1}, "Criteria1", {Criteria Column2}, "Criteria2"), 1)

In your case, the first criteria is the person, and the second criteria is the MAX date based on that person. This means we'd use another MAX COLLECT within the INDEX(COLLECT.

Try something like this:

=INDEX(COLLECT({Status Column}, {Person Column}, [Person Column]@row, {Created Date}, MAX(COLLECT({Created Date}, {Person Column}, [Person Column]@row))), 1)

Cheers,

Genevieve

Paul Newcome

I always bump into the #NESTED CRITERIA issue when trying to use a COLLECT inside of a COLLECT like this. To leverage the Created date, I typically create a help column on the target sheet that has the MAX/COLLECT portion in it and then reference the helper column in the INDEX/COLLECT.

Another option would be to have new forms populate the top of the sheet so that their most recent update is at the top. Then you can take advantage of the MATCH function stopping on the first MATCH to write out a straightforward INDEX/MATCH.

=INDEX({Status Column}, MATCH([Name Column]@row, {Name Column}, 0))

If you need the form to populate at the bottom of the sheet, you can still skip over trying to leverage a sys gen column by using an INDEX/COLLECT with a COUNTIFS. Basically we use the COLLECT to pull together all Status cells based on the name. Then is we count how many times that name is in the sheet, we can use that to tell the INDEX to pull the last entry from the COLLECTed cells.

=INDEX(COLLECT({Status}, {Name Column}, @cell = [Name Column]@row), COUNTIFS({Name Column}, @cell = [Name Column]@row))

One thing to keep in mind is that neither of my last two solutions will hold up if the form sheet gets sorted.