Is there a way to capture the latest comment on a sheet?

MarkCep

In a project schedule sheet, where comments are made on rows/tasks, I want to grab the comment that was made last on that project sheet to use in a summary report.

I know you can view all the sheet comments, but I only want to capture the last comment made on the sheet.

Goal is that the summary sheet will always have that last comment made in that project sheet without having to go the project sheet to view all of the comments.

Thanks

Nick Korna

Hi @MarkCep,

If you add the system generated "Latest Comment" and "Modified" columns then you can use the following:

=INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1)

If you wanted to trim out the name then you can:

=MID(INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1), FIND("-", INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1)) + 2, LEN(INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1)))

You can then use a sheet summary report to grab the relevant value from each project sheet (if I've understood that they're separate sheets correctly).

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post!

Nick Korna

Hi @MarkCep,

If you add the system generated "Latest Comment" and "Modified" columns then you can use the following:

=INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1)

If you wanted to trim out the name then you can:

=MID(INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1), FIND("-", INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1)) + 2, LEN(INDEX([Latest Comment]:[Latest Comment], MATCH(MAX(Modified:Modified), Modified:Modified), 1)))

You can then use a sheet summary report to grab the relevant value from each project sheet (if I've understood that they're separate sheets correctly).

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post!

MarkCep

Nick, you're a wizard, I can save some of my brain cells. This seems to work. Thank you.

Nick Korna

No problem, happy to have helped! 🙂

MarkCep

Hi Nick

For some reason the formula is not working now. I do not get an error; the cell remains blank with the formula.

Here is my formula.:

=INDEX([Latest Comment]:[Latest Comment], MATCH(MAX([Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper]), [Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper]), 1)

The "Notes Last Modified Date - Helper" is the Modified Date Column.

MarkCep

I think my issue is if another row is updated, but not by a comment, then that row with no comment in the "latest comment" column is blank.

Can this formula be revised to only look at rows in the Latest Comment Column that has data? to capture?

nanalyly

If you want to automate this, you can set up a trigger in the Apps Script to run the function at regular intervals or upon specific events (like editing the sheet).

Connections Unlimited