I need to show most recent row on a dashboard
I am trying to figure out he MAX(COLLECT and INDEX formulas. I have an input sheet for 8 seperate facilities and want to show the most recent status for each facility in a dashboard. I have been trying to figure out the I am trying to figure out he MAX(COLLECT and INDEX formulas but I am currently failing lol.
I want to show Treatment Plant Satus and Description of Status any help would be greatly appreciated.
Answers
-
Hey @Mandrada
I happy to help. If I'm understanding the screenshot correctly, this is one of your 8 sheets and it is only for the Bakersfield plant.
If this is true, you can make use of the Summary Fields (find this on the right hand ribbon down your sheet) to gather the data for your dashboard.
In my example, I will name the first Summary Field "Max Created". This finds the max date and the field MUST be a DATE field
=MAX(Created:Created)
The second field I will call "Max Description" and this field is a Text/Number field
=INDEX([Description of Status]:[Description of Status], MATCH([Max Created]#, Created:Created, 0))
Note I'm referencing the first summary field as the Match in the second summary field.
You will then be able to pull these fields into your dashboard either as metrics widgets or, you can gather 8 of them (they must be individually created on each sheet) by using a summary field report with all 8 sheets in the same report. If you use the report method, make sure all 8 sheets have the summary fields named EXACTLY the same.
Will this work for you?
Kelly
-
Thank you for your response!
I have tried =MAX(Created:Created) it returns =MAX(Created:Created)
-
Hi @Mandrada
I hope you're well and safe!
Can you share some screenshots of the formula and where you're adding it? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to 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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey @Mandrada
To make sure we're talking about the same thing:
then in the formula field
Are we on the same page?
Kelly
-
Thank you for your help so far I have made some progress :)
-
I have my summary fields and dashboard working great, thank you very much.
One more question, is there a way to have the MAX created date also look at the most recent time if more than one entry is submitted each day?
Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!