Issues with Max Collect formula and OR condition
I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in date columns) based on whether the status is 1 of 2 values (Draft or "Submitted for Approval"). I've tried with the contains but just not working.
This is the formula I'm using. It works with one value, Draft. I need to say give me the value if it is Draft or Submitted for Approval. Thanks for the help.
MAX(COLLECT({ReportAsOf}, {RepStatus}, "Draft"))
Best Answer
-
Max() will accept multiple sets of values - try:
MAX(COLLECT({ReportAsOf}, {RepStatus}, "Draft"),COLLECT({ReportAsOf}, {RepStatus}, "Submitted"))
If that doesn't work, you can always add a helper column with an if(or()) to force a single value if RepStatus is Submitted or Draft and then run your collect() call on that.
Answers
-
Max() will accept multiple sets of values - try:
MAX(COLLECT({ReportAsOf}, {RepStatus}, "Draft"),COLLECT({ReportAsOf}, {RepStatus}, "Submitted"))
If that doesn't work, you can always add a helper column with an if(or()) to force a single value if RepStatus is Submitted or Draft and then run your collect() call on that.
-
Brilliant! It worked perfectly. Thank you so much!
Susan
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!