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



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Steve Burggraf


    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.


    Project 2

  • Genevieve P.

    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.



    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!