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!
Answers
-
I go this to work:
=MAX(COLLECT({Production Priorities Range 1}, {Production Priorities Range 2}, [HELPER COLUMN - Project Number and Suffix]1))
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!