MAX COLLECT Formula

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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.