Displaying the most recent Comment

Tlabeau

Good Morning,

I have a dashboard that displays information for a morning meeting, it is all numbers, percentages and charts. I have had no problem with it up until now, I have been asked to add a comment function. Where the meeting head can click, then type a comment to display onto the dashboard before he prints it and goes to the meeting. I created another sheet, then used a form linked to the comment box to give him his easy input.

Where my trouble is, I cannot get the formulas to play nice with the sentence. They work swimmingly for numbers all day. Ive tried a bunch of different ways, but I am still learning Smart sheet so it has been eluding me. My code on the sheet that feeds the dashboard works and feels solid, this is it below.

=IF([12/16 Update 3]17 = [12/16 Update 3]10, "1", "0")

I use this to check the dates, paired with this.

=IF([12/16 Update 2]17 = "1", [Display Comment]4, "Date Mismatch")

Then I have a final check for errors before displaying

=IFERROR([Display Comment]2, "No Comment")

I just cannot seem to get smartsheet to gather the sentence, I need it to gather only the newest sentence, and to not display an old one because there has been no new input. My current attempts are to automate row removal based off of date on the separate comment sheet, to ensure that the sheet stays almost empty, so that the references can be tied to row 2.

It works in theory, and that's barely. It cannot handle two comments in one day for starters, and sometimes smart sheet will start a new row at row three and leave row two blank to vex me. Its crude, and I am unhappy with it, but my attempts to improve it have not had success.

I would love some advice so that I can be done with this comment situation, the answer feels just out of reach that it is driving me crazy.

Thanks,

Tom

Dale Murphy

@tlabeau Just for context, your comment is a column in a sheet, not a comment/conversation per the SmartSheet vernacular?

If I got that right, then you want to allow someone to submit a form (which is one column only?) and have the contents of that submission show on a dashboard. ((Cool idea ... I may have to use it! ))

If you are already using a form to collect the comment, I would build a report to catch the latest row from the sheet and use a report widget to display that on the dashboard. To ensure the report sees only the latest comment, build a formula on the sheet that calculates a maximum value that the report filters on. (I use two columns: one that inserts an auto number that increments for the new row, and then a column with this formula: =IF([Row ID]@row = MAX([Row ID]:[Row ID]), 1, 0) to return a one for the latest row)

With this approach, you also getting a running log of the comments.

dm

Paul Newcome

@Dale Murphy I personally like metrics widgets better than forms, so I use a Sheet Summary field in a similar way.

=INDEX(Comment:Comment, MAX([Auto-Number]:[Auto-Number]))

As long as ALL entries are via form populating at the bottom of the sheet and not being sorted/deleted, then you can just use the MAX function for the row # portion of the INDEX function.

Paul Newcome

Are you able to provide some screenshots with mock data for context?

Dale Murphy

@tlabeau Just for context, your comment is a column in a sheet, not a comment/conversation per the SmartSheet vernacular?

If I got that right, then you want to allow someone to submit a form (which is one column only?) and have the contents of that submission show on a dashboard. ((Cool idea ... I may have to use it! ))

If you are already using a form to collect the comment, I would build a report to catch the latest row from the sheet and use a report widget to display that on the dashboard. To ensure the report sees only the latest comment, build a formula on the sheet that calculates a maximum value that the report filters on. (I use two columns: one that inserts an auto number that increments for the new row, and then a column with this formula: =IF([Row ID]@row = MAX([Row ID]:[Row ID]), 1, 0) to return a one for the latest row)

With this approach, you also getting a running log of the comments.

dm

Tlabeau

No problem Paul, Ill share some screenshots.

@Dale Murphy Yes! It is there so that the production supervisor can put an explanation for any weird numbers, such as welding issues etc. That way its there, easy to remember and even easier to communicate.

This is the dashboard where it displays:

Here is the form, clicking the comments box opens the form for the meeting lead:

I wont get a chance to work on the page for a few hours, but I appreciate you both very much.

Paul Newcome

@Dale Murphy I personally like metrics widgets better than forms, so I use a Sheet Summary field in a similar way.

=INDEX(Comment:Comment, MAX([Auto-Number]:[Auto-Number]))

As long as ALL entries are via form populating at the bottom of the sheet and not being sorted/deleted, then you can just use the MAX function for the row # portion of the INDEX function.

Dale Murphy

@Paul Newcome Oh, that's brilliant. Agree that a metrics widget is cleaner (unless I suppose the comment is very long).

That might solve for a similar challenge I am having in our PMO ... thanks.

dm

Tlabeau

I whipped together the report method and it looks good, is working clean too. Ill see what the boss thinks, if he doesnt like the comment column box, ill switch it up to Paul's way. Either way, you two are brilliant, and I appreciate the help.

Paul Newcome

@Dale Murphy Even the Report widget can make longer comments look rough depending on widget size, text wrap, etc.. Either way it can get messy with longer comments unless you regularly have longer comments and a larger widget doesn't leave too much blank space on a regular basis.

I have also moved "most recent" into my metrics sheet in a cell so I can apply conditional formatting and have it pull through the metrics widget. Maybe allow the commenter to flag positive, negative, or neutral or indicate a priority and then pull that formatting through to the dashboard.

@Tlabeau The most important thing is what works for you. One of the nice things about Smartsheet is (as you are seeing) there are often multiple ways to approach a solution to fit your individual needs.