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

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

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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
    MarkCep ✭✭✭✭

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to have helped! 🙂

  • MarkCep
    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
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!