How do I only move most recent record to my Open Ticket Sheet?

AMJ

Capture.PNG

I receive a daily ticket report and I am looking to use Smartsheet to keep a list of the most recent data for each ticket. I am not the best at using formulas so I would love for some help. In the picture it shows the column headers for both my sheets I am trying to work in.

Genevieve P.

Hey @AMJ

If you have the same columns in both sheets, you can use one matching, unique value (e.g the INC Number) to find the row(s) associated with that number in the source sheet and pull back information.

If your source sheet will always be sorted with the LATEST row at the TOP of the sheet then you can use a straight-forward INDEX(COLLECT to bring back the first matching row. See:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-multiple-criteria

=INDEX(COLLECT({Column with value to bring back}, {Column with INC Number}, [INC NUMBER]@row), 1)

The 1 at the end is what tells it to bring back the first row down the list that matches the INC Number in the current sheet.

However if your original sheet could be sorted in numerous ways, then we'll need some sort of indicator for what the "latest" row is. Would that be the Created Date? Is that unique per-row, or unique per-incident?

If you have a Date column and you want to find the MAX date, I would first use an MAX(COLLECT in your second sheet's Created Date column.

=MAX(COLLECT({Column with value to bring back}, {Column with INC Number}, [INC NUMBER]@row))

Then you can reference this date as another criteria in your INDEX(COLLECT in all the other columns.

=INDEX(COLLECT({Column with value to bring back}, {Column with INC Number}, [INC NUMBER]@row, {Created Date Column}, [Created Date]@row), 1)

Does this make sense?

Cheers!

Genevieve

Eric Law

@amj Can you post what you are wanting? Like a mockup/example or anything like that. What do you mean by move, as your title suggested? Very difficult to assist without more information.

AMJ

Capture1.PNG

Example attached. As you can see, there are multiple records for 1 incident, and I only want to pull in the latest to my destination sheet from the 'data dump' sheet. Hope this helps.

Genevieve P.

Hey @AMJ

If you have the same columns in both sheets, you can use one matching, unique value (e.g the INC Number) to find the row(s) associated with that number in the source sheet and pull back information.

If your source sheet will always be sorted with the LATEST row at the TOP of the sheet then you can use a straight-forward INDEX(COLLECT to bring back the first matching row. See:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-multiple-criteria

=INDEX(COLLECT({Column with value to bring back}, {Column with INC Number}, [INC NUMBER]@row), 1)

The 1 at the end is what tells it to bring back the first row down the list that matches the INC Number in the current sheet.

However if your original sheet could be sorted in numerous ways, then we'll need some sort of indicator for what the "latest" row is. Would that be the Created Date? Is that unique per-row, or unique per-incident?

If you have a Date column and you want to find the MAX date, I would first use an MAX(COLLECT in your second sheet's Created Date column.

=MAX(COLLECT({Column with value to bring back}, {Column with INC Number}, [INC NUMBER]@row))

Then you can reference this date as another criteria in your INDEX(COLLECT in all the other columns.

=INDEX(COLLECT({Column with value to bring back}, {Column with INC Number}, [INC NUMBER]@row, {Created Date Column}, [Created Date]@row), 1)

Does this make sense?

Cheers!

Genevieve

AMJ

Thank you so much, Genevieve!!

Jbakertm

@Genevieve P. I am trying to make a "latest updates" section in a single sheet. I want it to return the text value of the most recent update in the "Accomplished this period" column when a new update is added.

The formula we have been using is this: =INDEX(AccomplishmentsThisPeriod15:AccomplishmentsThisPeriod62, MATCH([Update Helper]@row, [Update Helper]15:[Update Helper]62, 1))

But that doesn't always bring back the most recent added. I tried changing the 1 to -1, but it comes back with no matches (assuming because the test in Update Helper row 62 is empty). I also tried adding a date function in there, but it kept coming back as unparseable.

I also want the rest of the row to update with the most recent updates, like to date/time columns in the "Latest Updates" section.

Genevieve P.

Hey @Jbakertm

The time in your Update Helper column for the row with the formula does not match the time in Row 19, where you say the data should be pulled from.

Instead of INDEX(MATCH, what about using INDEX(COLLECT? You can use the MAX function to look into the Modified date column and only return the row with the MAX date.

Try:

=INDEX(COLLECT(AccomplishmentsThisPeriod15:AccomplishmentsThisPeriod62, Modified15:Modified62, MAX(Modified15:Modified62)), 1)

Cheers,

Genevieve

Jbakertm

@Genevieve P.

Thank you so much! That worked.