If Comment is not blank, then show the recent comment and date

bea.sun

Hi everyone,

I have a Follow Up Date column and a Comments column. I have predetermined dates under the Follow Up column. I would like a formula to populate the most recent Follow Up Date and the corresponding Comments on the parent rows only if the Comments are not blank.

Any help is appreciated! Thank you!

Andrée Starå

Hi @bea.sun

I hope you're well and safe!

To add to Tomasz's excellent advice/answer.

Here's another option.

Add the following formula to the Parent cell of the Follow Up Date column.

=MAX(COLLECT(CHILDREN([Follow Up Date]@row), CHILDREN(Comments@row), <>""))

Add the following formula to the Parent cell of the Comments column.

=INDEX(CHILDREN(), MATCH([Follow Up Date]@row, CHILDREN([Follow Up Date]@row), 0))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Tomasz Giba

Hi @bea.sun

Check if this (not perfect) solution will work for you :

Please follow these steps:

(1) Create 2 additional columns (I named them: Follow Up Date if comment and most recent comment)

(2) Make column formula in Follow Up Date if comment:

=IF(ISBLANK(Comments@row), "", [Follow Up Date]@row)

(3) Type formula into cell: Parent row and Follow Up Date column (circled red):

=MAX(CHILDREN([Follow Up Date if comment]@row))

(I could not understand correctly what you meant by "the most recent Follow Up Date", you may check if changing MAX to MIN would do what you want)

(4) Make column formula in most recent comment:

=IF([Follow Up Date if comment]@row = PARENT([Follow Up Date]@row), Comments@row)

(5) Type formula into parent tow Comment column:

=JOIN(CHILDREN([most recent comment]@row))

So as I said earlier this solution is not perfect for a number of reasons but it may be a starting point and we can go from there if you want.

Please let me know how it works.

Thanks!

Andrée Starå

Hi @bea.sun

I hope you're well and safe!

To add to Tomasz's excellent advice/answer.

Here's another option.

Add the following formula to the Parent cell of the Follow Up Date column.

=MAX(COLLECT(CHILDREN([Follow Up Date]@row), CHILDREN(Comments@row), <>""))

Add the following formula to the Parent cell of the Comments column.

=INDEX(CHILDREN(), MATCH([Follow Up Date]@row, CHILDREN([Follow Up Date]@row), 0))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

bea.sun

https://community.smartsheet.com/discussion/comment/342349#Comment_342349

Thank you so much, @Andrée Starå that worked wonders!! I had the same formula for Follow Up Date column but could not pull the formula for the Comments column. I appreciate it!!

And thank you, @Tomasz Giba! making another set of columns was my next option, so thank you for confirming my thoughts!