Summary Field return symbol for most recent entry

Hi, I've been searching and testing without much luck.

I have a task list that the project health is updated with each task. I added a column for last modified date.

I would like to have the summary field reflect the health on the row where the most recent modified date is. This summary field will be collected in a summary report across multiple sheets that in turn will be charted on a Dashboard for leadership to see the current health of various projects as of the last update. (In theory this sheet will be updated monthly.)


I've been trying combinations of INDEX, COLLECT, MAX to no avail. Even a simple =MAX(Modified:Modified) produces an invalid column name error. Any help would be appreciated.

Answers

  • John Shane
    John Shane ✭✭✭✭

    @Jess D

    What type of field did you use on your summary sheet? I created one with a date format and your =MAX(Modified:Modified) formula worked to give me the most recent change date. I did have to copy and paste your formula into the box to get it to work right as it gave me an error initially when I tried to click the column to set it as the range.

    Cheers,

    John

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this:

    =INDEX(Health:Health, MATCH(MAX(Moodified:Modified), Modified:Modified, 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jess D
    Jess D ✭✭

    Hi @John Shane, thank you, I think it was due to my using the field type text/number. Somehow I didn't realize formulas could be inserted into other types. I was able to replicate those results.


    Hi @Paul Newcome, thank you! I tried that out in multiple field formats but keep getting an #Unparsebale error. I tried text/number, date, and symbol types copying what you provided. Thoughts?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I notice I have a typo in one of the column references, so make sure the column names in the formula match exactly what you have in your sheet.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jess D
    Jess D ✭✭

    Thank you, @Paul Newcome . I should have caught that. I appreciate your second look and assistance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!