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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

  • Mandrada
    Mandrada ✭✭✭

    Thank you for your response!

    I have tried =MAX(Created:Created) it returns =MAX(Created:Created)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Mandrada

    To make sure we're talking about the same thing:

    then in the formula field

    Are we on the same page?

    Kelly

  • Mandrada
    Mandrada ✭✭✭

    Thank you for your help so far I have made some progress :)

  • Mandrada
    Mandrada ✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!