Index and collect most recent text and where not blank based on Max Modified date?

Options

I'm using the following formula to collect the most recent comment from children cells. We are having an issue when a user adds a comment and then deletes it and/or types a new comment within the same cell within the same minute. When this happens, the parent retains the blank cell "comment" or doesn't recognize the new comment. How can I solve this issue?

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(Modified@row)), CHILDREN(Modified@row), 0))


Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You could use a JOIN/COLLECT instead.


    =JOIN(COLLECT(CHILDREN(), CHILDREN(), @cell <> "", CHILDREN(Modified@row), @cell = MAX(CHILDREN(Modified@row))), CHAR(10))

  • ErinTyler
    ErinTyler
    edited 04/24/24
    Options

    Hi @Paul Newcome. I'm not sure that accomplishes the index ignoring the cells with no status update data (deleted) and choosing the next to most recent status update comment that has data... I think the main issue is that the Modified column does not timestamp milliseconds and when 2 or 3 updates are made within the same minute. Should I use a custom timestamp field formula to collect data/time with milliseconds? Does such a formula/functionality exist?


    Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!