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
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!