Returning Max Date from Another Sheet with Conditions

Lauren Acker

Hello,

I am trying to gather the max date from a row on another sheet if that row has a matching cell to one on the current sheet. Below is a screenshot of the current sheet. I am trying to fill the Last Action Date cell if the Originating WO# cell matches the one on the other sheet.

Other sheet:

I would like the formula to choose the latest date (from between Sent, Date Conf, Shipped, Received) on the other sheet if the WO numbers match.

I've tried a few combinations of Index, Match, Max, and Collect but can't seem to get the right formula.

Thank you!

Paul Newcome

You will want to use a MAX/COLLECT...

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet WO Column}, [WO Column]@row)

Paul Newcome

The easiest way to do that would be to add another date column on your source sheet. In that column, you would use...

=MAX([Date Conf.]@row, Shipped@row, Received@row)

This will return the latest date of the three columns. Then in the cross sheet formula provided earlier, you would reference this new date column.

Paul Newcome

You will want to use a MAX/COLLECT...

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet WO Column}, [WO Column]@row)

Lauren Acker

Paul,

Is there a way to get it to return the latest date in that row? For example, if the Shipped and Received columns are blank, I want it to return the Date Conf. date since that is the latest date:

Paul Newcome

The easiest way to do that would be to add another date column on your source sheet. In that column, you would use...

=MAX([Date Conf.]@row, Shipped@row, Received@row)

This will return the latest date of the three columns. Then in the cross sheet formula provided earlier, you would reference this new date column.

Andre Drinkwater

https://community.smartsheet.com/discussion/comment/238764#Comment_238764

Thanks @Paul Newcome, I struggled with this for 2 hours and was about to give up. This formula works perfect for me.

Paul Newcome

Happy to help. 👍️