How does COLLECT() with String Criterion Work?
I have a simple formula (I thought) that seems to broken. It seems to be centered around the existence of a String in the Criterion Parameter of COLLECT().
Formula: =IF(MIN(COLLECT(Date:Date, [Work Type]:[Work Type], "Mandatory"))=Date1, Hours1-6, Hours1)
Result: #UNPARSEABLE
Is it not possible to have a string Criterion like "Mandatory" in COLLECT()?
Best Answer
-
Are you using the correct column headers? The formula works for me, to the degree that it parses and returns a value. There is no issue putting a string in text so long as it is surrounded by quotes as you've done.
I'll add it's not fully clear what you are trying to accomplish with this formula. If you can explain the desired functionality I could potentially give a more useful response.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Answers
-
Are you using the correct column headers? The formula works for me, to the degree that it parses and returns a value. There is no issue putting a string in text so long as it is surrounded by quotes as you've done.
I'll add it's not fully clear what you are trying to accomplish with this formula. If you can explain the desired functionality I could potentially give a more useful response.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Dan,
I've spent years of my life being careful with formulas like this making sure every character is precisely what it should be... and yet, that must have been my problem here.
I promise I tried re-typing everything several times in several different simpler versions before I came here. Perhaps it was a passing bug or I just needed to take a walk outside? Hah!
Thanks for convincing me it was worth another try!
Regards,
Nick M.
-
@Nicholas Mathern it happens to the best of us! Happy to help.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!