Linking Most Recent Status from Another Sheet

smarterwithsmart

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.

Genevieve P.

Hi @smarterppsd

I would suggest creating a helper column in your "Latest Status" sheet that brings in the most recent date, based on the Ticket Number. Then you can have the Status refer to both the Ticket Number and the most recent date to pull the correct Status.

To bring in the MAX date using cross-sheet references, you can use a MAX(COLLECT combination (this needs to be build into a Date type of column):

=MAX(COLLECT({Date range in source sheet}, {Ticket Number in Source Sheet}, [Ticket Number]@row))

Then, in your Status column you can build the following:

=INDEX(COLLECT({Submitted Status in Source Sheet}, {Ticket Number in Source Sheet}, [Ticket Number]@row, {Date range in source sheet}, [Max Date]@row), 1)

I used MAX because I wanted to pull the newest date, or most recent date, instead of MIN which would pull the oldest date.

Let me know if this will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @smarterppsd

I would suggest creating a helper column in your "Latest Status" sheet that brings in the most recent date, based on the Ticket Number. Then you can have the Status refer to both the Ticket Number and the most recent date to pull the correct Status.

To bring in the MAX date using cross-sheet references, you can use a MAX(COLLECT combination (this needs to be build into a Date type of column):

=MAX(COLLECT({Date range in source sheet}, {Ticket Number in Source Sheet}, [Ticket Number]@row))

Then, in your Status column you can build the following:

=INDEX(COLLECT({Submitted Status in Source Sheet}, {Ticket Number in Source Sheet}, [Ticket Number]@row, {Date range in source sheet}, [Max Date]@row), 1)

I used MAX because I wanted to pull the newest date, or most recent date, instead of MIN which would pull the oldest date.

Let me know if this will work for you!

Cheers,

Genevieve

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

Thank you @Genevieve P

That solution works perfectly. 👍️

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

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