MAX COLLECT Formula

Design Department

I have one table for shipping (Production Priorities) :

And another table for Project Revenue Recognition (Revenue Recognition Forecast):

I would like to write a formula to auto-populate the max ship date for each project.

=MAX(COLLECT({Production Priorities Range 1}:{Production Priorities Range 1}, {Production Priorities Range 2}, [HELPER COLUMN - Project Number and Suffix]1)

Production Priorities Range 1 = Ship Date

Production Priorities Range 2 = HELPER COLUMN - Project Number and Suffix

The formula keeps coming up as #UNPARSEABLE

Help!

Design Department

I go this to work:

=MAX(COLLECT({Production Priorities Range 1}, {Production Priorities Range 2}, [HELPER COLUMN - Project Number and Suffix]1))

Mark Cronk

Hi @Design Department ,

I can't follow your formulas from the information you provided but maybe I can get you far enough that you can solve it.

The syntax for COLLECT is COLLECT( range, criterion_range1, criterion1)

Expressions in {} are ranges so you don't connect them with a : like you do column or row ranges.

Your formula would be:

=MAX(COLLECT({Production Priorities Range 1}, {Production Priorities Range 2}, [HELPER COLUMN - Project Number and Suffix]1)

This says look in your ship date range and bring back the maximum date in the row where the [Helper Column - Project Number & Suffix] equals the value in row 1 of [Helper Column - Project Number & Suffix]. I can't understand your Criterion logic but it may make sense to you.

If this doesn't solve your problem, I'm happy to help more.

Mark