Is there a way to find the last non empty cell in a column similar the LOOKUP function in Excel?

Options

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Will all of the cells above be filled in? Exactly how are you wanting to use this data point?

  • Steve Burggraf
    Options

    Hello,

    I have a dashboard showing a project list. The projects are defined by individual tasks, we usually do not start a new task until the last one is complete. For each project, I would like to show the status of the current or last task. My workaround is to have separate cell in the project sheet that is referenced to the last task and then link this cell back to my report. I hope these examples help explain what I’m trying do. There is probably a simple way to do this.

    Dashboard


    Project 2


  • Genevieve P.
    Options

    Hi @Steve Burggraf

    You could use a formula in that Project Status column to automatically bring in the most recent Status Ball.

    I would personally add in a Created Date system column, then use this to find the MAX date in that column where the Status is not blank.

    For example:

    =INDEX(Status:Status, MATCH(MAX(COLLECT([Created Date]:[Created Date], Status:Status <>"")), [Created Date]:[Created Date]))

    Then I actually like your idea of a Report to show this, or you could use a Metric Widget directly from the Sheet if you'd prefer.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!