Linking Most Recent Status from Another Sheet


I'm trying to set up a ticket system and having a little trouble figuring out how to accomplish my goal.

Technicians have their own sheets and forms to submit updates on their tickets. Multiple techs can work on the same ticket ID. Their form submissions get copied to a master form. A simple vlookup to match the ticket # with the status doesn't work as they don't necessarily get copied in the correct order.

Here's the simple vlookup results.

And here is the Master list it is pulling from.

Seems as if the vlookup pulls from the bottom of the sheet, which would not be a problem except for the fact that my technicians aren't always timely in their submissions and sometimes backdate.

I've read a little about Collect and MIN but am unsure of how to connect it to the ticket number.

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also use a JOIN(COLLECT(...........................), delimiter). That way if there are multiple submissions for that ticket on the same date, you can capture them all. Since you don't really have a way of determining based on time which entry for the day is the most recent, this would allow you to display them both in the same cell.

    Just another option if there is a need to account for multiple entries on the same date for the same ticket.

  • smarterwithsmart
    smarterwithsmart ✭✭✭✭

    Thank you @Genevieve P

    That solution works perfectly. 👍️

  • mgilkessmith
    mgilkessmith ✭✭✭✭

    @Paul Newcome how would you INDEX/COLLECT from multiple sheets? I am building a ticketing system where each technician has their own working sheet. The rows are copied to their sheets via automation. I would like for the individual sheets to update the main sheet with the ticket statuses. The statuses are "Not Started", "In Progress" and "Resolved".

    Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @mgilkessmith I would suggest using a report to compile each of the individual sheets instead.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!