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
Alex
Best Answers
-
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)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
You would follow the pattern
=MAX(COLLECT(range to collect, criteria range 1, criteria 1, criteria range 2, criteria 2, ..................................)
=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row), {Protocol approval Country of Registration Column}, @cell = "Ukraine"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Great, it works. Thanks a lot!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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); [???]))
-
You would follow the pattern
=MAX(COLLECT(range to collect, criteria range 1, criteria 1, criteria range 2, criteria 2, ..................................)
=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row), {Protocol approval Country of Registration Column}, @cell = "Ukraine"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
You are going to want to add in 2 columns. Hierarchy and [Task Helper]. Both will be text/number type.
In Hierarchy:
=COUNT(ANCESTORS(Task@row))
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"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!