Finding the biggest date

MARC SOEHNGEN

Hello All,

Have no clue on how to approach this. I have 2 smartsheets as follows:

• Main SmartSheet: List of main projects and associated ID number.
• Sub SmartSheet: List of sub projects linked to the main project through the ID number with status and due dates. Hence I have multiple sub-project for each project with due dates.

I need to display the number of subproject still active --> done.

But I also need to show the latest due date of all sub projects for one main project.

Hence I need to look up (INDEX/MARC) all sub projects linked to the main project and then run a MAX function on the due dates.

No clue how to set the order. I assume INDEX, MATCH and MAX, but how to combine?

Hope someone can help.

regards

Marc

Paul Newcome

I would suggest a MAX/COLLECT along the lines of...

=MAX(COLLECT({Dates}, {ID Numbers}, "12345"))

Paul Newcome

Yes. The formula above will do just that. You will need to make sure you select the appropriate column for each of the cross sheet references, and you can replace "12345" with a cell reference to whichever cell contains the ID Number you want to pull based on.

Paul Newcome

I would suggest a MAX/COLLECT along the lines of...

=MAX(COLLECT({Dates}, {ID Numbers}, "12345"))

MARC SOEHNGEN

Thanks you for your fast reply.

Sorry I think I was not clear. I need to display the info on the Main SmartSheet. Hence I need to take the Project ID from the main SmartSheet and find all Project IDs and pick the max date from these.

Paul Newcome

Yes. The formula above will do just that. You will need to make sure you select the appropriate column for each of the cross sheet references, and you can replace "12345" with a cell reference to whichever cell contains the ID Number you want to pull based on.

MARC SOEHNGEN

Hello Paul, Worked PERFECT! THANKS!!!! wish you a nice weekend, even though we still have 1 day to go. regards Marc

Paul Newcome

Happy to help. 👍️