Get a date from the the newest modified column of another sheet both need to have same ID

Zurisadai Rodriguez

First Sheet

*Service Ticket ID

*Estimated Finish Date <---With formula to be pull from second sheet

Second Sheet

*Service Ticket ID

*Estimated Finish Date to be entered manually

*Modified Date (System Colum)

I'm trying to create a Formula that will be placed in the First Sheet (Estimated Finish Date), this Estimated Finish Date need to be from the same Service Ticket ID, and also needs to be the latest row modified.

I tried using Max/Collect,Collect by itself, VLOOKUP. But just can't make it work.

Thank you !

Zurisadai Rodriguez

I managed to create the new formula:

=INDEX(COLLECT({Estimated Finish Date Sheet 2}, {ServiceTicketNumber Sheet 2}, [Service Ticket Number]@row, {Modified Sheet 2}, MAX({Modified Sheet 2})), 1)

It seems to work correctly.

Don Wilcox

@Zurisadai,

Have you tried using index/match?

for ex:

=index(sheet2[Estimated Finish Date to be entered manually], match([Service Ticked ID]@row, sheet2[Service Ticket ID],0))

This matches the Service Ticket ID field on your sheet 1 to the Service Ticket ID on sheet 2 and gets the Estimated Finish Date from that same row. Remember, if you try this, for the sheet2 references, click the references link in the pop-up, select sheet 2 (from the list of sheets) and the applicable column once the sheet subset is displayed.

Hope this helps.

Zurisadai Rodriguez

https://community.smartsheet.com/discussion/comment/326383#Comment_326383

@Don Wilcox This formula is missing something, I need this to be the last modified row in the Sheet2, since this will have multiple rows with the same Service Ticket ID.

Mike TV

How do your rows get added to the sheet? For example, if you use a Form to get new rows added to the sheet, on the Settings area of the Form setup you can set whether the new submission is added to the top or bottom of the sheet. If you're adding rows with a Form, just make sure the Form adds the row to the top of the sheet. That way, when you Index Match or Index Collect, what you want to grab will always be the first thing with that Service Ticket ID and an Index Match should always grab what you're looking for I believe.

Zurisadai Rodriguez

I managed to create the new formula:

=INDEX(COLLECT({Estimated Finish Date Sheet 2}, {ServiceTicketNumber Sheet 2}, [Service Ticket Number]@row, {Modified Sheet 2}, MAX({Modified Sheet 2})), 1)

It seems to work correctly.