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

Aliaksandr Ilyuk

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

Alex

Paul Newcome

Here's a breakdown of the COLLECT. Basically the way it works is you are collecting a set of data based on specific criteria that you set. So the first range is the range of data you want to collect. Then you would use a series of range/criteria to determine what cells from the first range you want.

So using your above as an example, we want to collect data from the Last Modified column of Table 1

=COLLECT({Protocol approval Last Modified Column},

Then we want to collect those cells based on the range of the Trade Name column of Table 1

=COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column},

where the criteria of it matching the data in the parent row of the Task column

=COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row))

Now we want the MAX of the data we pulled using our COLLECT, so we wrap the COLLECT in the MAX and see how it works.

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