Max(Collect()) To pull in the most recent entry

kaia2001

Hi!

I am attempting to use MAX(COLLECT()) to pull in the most recent entry from a manually maintain status log. If the date in "Week off" column on the log is the most recent date and the Project name matches the name on the row in the "Data Sheet" then I want to populate the entries in the "Weekly Highlight" column.

Status Log:

Data Sheet:

=IF(AND({GSC | Project Status-Week Off} = MAX({GSC | Project Status-Week Off}:{GSC | Project Status-Week Off}),{GSC | Project Status-Project}=[Project Name]@row), {GSC | Project Status Range-Weekly Highlight},"")

Genevieve P.

Hi @kaia2001

I would use an INDEX(COLLECT for this, see: Formula combinations for cross sheet references

For example:

=INDEX(COLLECT({Column to return}, {Criteria Column}, "Criteria 1", {Date Column}, "Date"), 1)

However, since we're looking for the MAX date but from a specific criteria, we'll need to use a MAX(COLLECT inside of this formula as well:

=INDEX(COLLECT({Column to return}, {Criteria Column}, "Criteria 1", {Date Column}, MAX(COLLECT({Date Column}, {Criteria Column}, "Criteria 1"))), 1)

So in your case, something like this:

=INDEX(COLLECT({GSC | Project Status Range-Weekly Highlight}, {GSC | Project Status-Project}, [Project Name]@row, {GSC | Project Status-Week Off}, MAX(COLLECT({GSC | Project Status-Week Off}, {GSC | Project Status-Project}, [Project Name]@row))), 1)

See: Create cross sheet references to work with data in another sheet and Create a Cell or Column Reference in a Formula

Cheers,

Genevieve