Sheet Update Display on Dashboard

April Tucker

Hi Smartsheet Community!

I've reviewed and tried a couple different formulas for my question, but none have seemed to work. I am trying to display on a dashboard the last time a form was submitted and entered into a grid/sheet. I can manually enter the update into the dashboard, but I would like to automate it to pick up the last entry submitted. I only need the date, not the time (which is automated as well on the sheet). Here's what I'm looking to capture:

The formulas I have tried are:

=MAX (resulted in "0")

=MAX({RP3 UAT Issues Intake Grid - Test Master Range 3})

and =INDEX + MAX(COLLECT...) (Resulted in "unparseable"), where Range 5 = Date/Time Received column selection and Range 6 = Row 7 selection (last row displayed).

=INDEX({RP3 UAT Issues Intake Grid - Test Master Range 5}:{RP3 UAT Issues Intake Grid - Test Master Range 5}, MAX(COLLECT({RP3 UAT Issues Intake Grid - Test Master Range 6}:{RP3 UAT Issues Intake Grid - Test Master Range 6}, {RP3 UAT Issues Intake Grid - Test Master Range 5}:{RP3 UAT Issues Intake Grid - Test Master Range 5}, <>""))

Clearly, I'm not doing something right. ;) Appreciate any assistance!

April

Andrée Starå

@April Tucker

Try something like this.

=MAX([Date/Time Received]:[Date/Time Received]) + ""

Did that work?

Mark Cronk

Hi @April Tucker ,

Try creating a grid summary field with the formula =MAX(created:created). Make sure the field properties are set to date otherwise you'll get an invalid column type error. Add the summary field as a dashboard metric.

Work?

Mark

April Tucker

Hi Mark,

I guess I should start with, how do I create a sheet summary? I know how to set/edit columns with similar information, but I haven't used a sheet summary before. I'm using a metrics sheet to retain all the items I'm pulling into the dashboard. Should I be using a cell/prompt on the grid sheet to do this?

Thanks!

April

Mike Wilday

For more on sheet summary see:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Andrée Starå

Hi @April Tucker

To add to Mark's excellent advice/answer.

If you'd want to show the time as well, you can add +"" at the end of the formula, and then it doesn't have to be a Date Type Column, but it will work either way.

=MAX(created:created)+""

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

April Tucker

Hi all,

Sorry, I'm not getting this at all. I created the sheet summary as directed, but where do I enter that on the grid and/or metrics page to get it to populate what I want on the dashboard? I'm missing the connection. Steps so far:

1) Master grid sheet - added new sheet summary

2) Metrics page where I tried making the connection earlier. Where/how do I input or connect to the sheet summary?

Thank you!

April

April Tucker

3) Added as a metric to the dashboard directly:

Used formula = max(created:created) and =max([created:created]) but neither work.

Andrée Starå

@April Tucker

No worries!

Happy to help!

You'll have to add the formula here:

Did that work?

April Tucker

Hi Andree,

No, here are the results of adding the formulas to the date area:

Andrée Starå

@April Tucker

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

April Tucker

Hi Andree,

Thank you for the offer, but my company has very strict rules about sharing information. Do I need to select the column in which I want to have the information coming from? I have two date areas - one for test date and one for when the form was received and automatically fed into the worksheet grid. Maybe that's the issue?

Thank you!

April

Andrée Starå

@April Tucker

I'm always happy to help!

Can you share a screenshot of the column names? I think that is the issue.

Also, make sure that you don't have any error messages in the date column.

April Tucker

No error messages in the columns. The test date is from the form manual input and the Date/Time Received is an auto-populated date/time stamp of when the form was received.

Andrée Starå

@April Tucker

Try something like this.

=MAX([Date/Time Received]:[Date/Time Received]) + ""

Did that work?

April Tucker

Yes, that solved it - thank you!! :) I knew there was some type of connection that wasn't being made.

Andrée Starå

@April Tucker

Excellent!

You're more than welcome!

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