How to pick the latest date from Table 1 and enter it to the cell of Table 2?

Table 1 is a list of products that contains among others columns Trade Name, Country, Crop and Last Modified (Auto). Within the rows, one trade name can refer to different countries or crops.

In Table 2 there are only Trade Name and Date columns.

I need to choose the latest date from the rows of Table 1 referred to the specified trade name and place it in one cell of the date column of Table 2.

Thank you in advance


Best Answers


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

    Based on your question, I believe there may be a solution using a MAX/COLLECT formula, but I want to be sure I am understanding you correctly.

  • Aliaksandr Ilyuk
    Aliaksandr Ilyuk ✭✭✭✭
    edited 02/11/20

    Hello Paul.

    This is Table 1:

    This is Table 2:

    I need the latest dates from Table 1 to be placed in the corresponded cells of Table 2.

    I have thought about MAX & COLLECT formulas but I hardly understand the logic and the syntax of the last one. Could you help with this?

    Thank you.

  • Aliaksandr Ilyuk
    Aliaksandr Ilyuk ✭✭✭✭

    Great, it works. Thanks a lot!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that one may be found here.

  • Aliaksandr Ilyuk
    Aliaksandr Ilyuk ✭✭✭✭

    If I want to add one more criteria (max. date should refer to Ukraine only), I need to collect data from {Protocol approval Country of Registration Column}. What should I add to the formula?

    =MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row); [???]))

  • Aliaksandr Ilyuk
    Aliaksandr Ilyuk ✭✭✭✭

    Currently the formula is the following:

    =MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row), {Protocol approval Country of Registration Column}, @cell = "Ukraine"))

    and it perfectly works in green cell.

    In case of a bit more complicated hierarchy of tasks, how to set up the formula (for the yellow cell) to capture the matching criteria from the highest level?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to want to add in 2 columns. Hierarchy and [Task Helper]. Both will be text/number type.

    In Hierarchy:


    In [Task Helper]:

    =IFERROR(INDEX(ANCESTORS([Task Helper]@row), MATCH(0, ANCESTORS(Hierarchy@row), 0)), [Task Helper]@row)

    Then adjust your formula to look more like this...

    =MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = [Task Helper]@row, {Protocol approval Country of Registration Column}, @cell = "Ukraine"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!