# MAX COLLECT Formula

Options

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!

Tags:

• Options

I go this to work:

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!